cancel
Showing results for 
Search instead for 
Did you mean: 

Text to columns. arrgh!

Luzern
Seasoned Pro
Posts: 2,884
Thanks: 235
Fixes: 2
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
minkey
Grafter
Posts: 386
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
Grafter
Posts: 386
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
Seasoned Pro
Posts: 2,884
Thanks: 235
Fixes: 2
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.
Community Veteran
Posts: 1,571
Thanks: 3
Registered: 13-04-2007

Re: Text to columns. arrgh!

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