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

use the trim function to remove the unwanted space
"abugoli" <(E-Mail Removed)> wrote in message
news:2678A7CE-12FF-4229-BEC2-(E-Mail Removed)...
> 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

On Jun 26, 12:38 pm, abugoli <a...@dalia.org.il> 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

-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" <(E-Mail Removed)> wrote in message
news:2678A7CE-12FF-4229-BEC2-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:2678A7CE-12FF-4229-BEC2-(E-Mail Removed)...
> > 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
>
>
>