cancel
Showing results for 
Search instead for 
Did you mean: 

Text to columns. arrgh!

Luzern
Hero
Posts: 4,823
Thanks: 872
Fixes: 9
Registered: ‎31-07-2007

Text to columns. arrgh!

I am using Excel to analyse my BT bill. In it is a column for duration of call in format hh:mm:SS
I want to separate the elements in to separate columns so I may recalculate the minutes in decimal. However when I try, all I get in each column are 0s, so that 1:25:23 becomes
00:00:00
What may I be doing wrong, and secondly how should I go about my task, or is there a better way to recalculate the BT data as minutes and parts of minutes in decimal form?
No one has to agree with my opinion, but in the time I have left a miracle would be nice.
4 REPLIES 4
minkey
Rising Star
Posts: 418
Thanks: 15
Fixes: 2
Registered: ‎22-07-2007

Re: Text to columns. arrgh!

The way I would do it is just do a text to columns on the duration column. Do it as fixed width and separate at each colon. On the next screen you can choose to not import the colon columns then you should have each number in a separate field.
EG
00:05:37  becomes: 0 5 37
Jeff
minkey
Rising Star
Posts: 418
Thanks: 15
Fixes: 2
Registered: ‎22-07-2007

Re: Text to columns. arrgh!

Another way is to use the functions HOUR() MINUTE() and SECOND() functions on the time cell.
Jeff
Luzern
Hero
Posts: 4,823
Thanks: 872
Fixes: 9
Registered: ‎31-07-2007

Re: Text to columns. arrgh!

Thanks Grin
No one has to agree with my opinion, but in the time I have left a miracle would be nice.
samuria
Grafter
Posts: 1,581
Thanks: 3
Registered: ‎13-04-2007

Re: Text to columns. arrgh!

The column needs selecting and the format setting for time as well