delete spaces
 

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
--
abugoli

delete spaces
 

use the trim function to remove the unwanted space
"abugoli" wrote in message
...
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
--
abugoli

delete spaces
 

On Jun 26, 12:38 pm, abugoli wrote:
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
--
abugoli

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

delete spaces
 

-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,

"abugoli" wrote in message
...
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
--
abugoli

delete spaces
 

Thank u G., for your complete solution
--
abugoli


"George Nicholson" wrote:

-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,

"abugoli" wrote in message
...
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
--
abugoli