delete spaces

[Guest]

in my excel file there is a column of times in the format hh:mm:ss.
I want to convert it to decimal value (i.e. 02:15:30 will be 135.5 min.).
the problem is that every value starts end ends with spaces, and because of
that I can't use the functions hour(),minute(),second().
I can delete the spaces one by one, but it's a large file and a lot of work.
how can I delete the spaces ??
thanks a lot

 

[Naraine Ramkirath]

use the trim function to remove the unwanted space

 

[meh2030]

in my excel file there is a column of times in the format hh:mm:ss.
I want to convert it to decimal value (i.e. 02:15:30 will be 135.5 min.).
the problem is that every value starts end ends with spaces, and because of
that I can't use the functions hour(),minute(),second().
I can delete the spaces one by one, but it's a large file and a lot of work.
how can I delete the spaces ??
thanks a lot

Look up the TRIM Function, it should do what you are looking for. If
you can't embed the hour function in the trim function (=TRIM(HOUR())
then you could do a TRIM, copy the values, do a paste special values,
and then use the HOUR, ect. Functions. If for what ever reason it
doesn't, then you can write a small macro to remove the spaces.

Matt

 

[George Nicholson]

-Create a new "Helper" column
- In the Helper Column, add the formula: = Trim(B2)
(Where B is the column you want to Trim spaces from)
- Replace the old B values with the results from the Helper (Copy &
PasteSpecial(Values))
- Delete the Helper column

or

Do both steps at once. To convert your existing data to minutes-as-decimal,
use this in your helper column instead:
=(HOUR(TRIM(B2))*60)+MINUTE(TRIM(B2))+(SECOND(TRIM(B2))/60)

HTH,

 

[Guest]

Thank u G., for your complete solution