break data into columns

[Totti]

Hi everybody,
I have downloaded some data from a file into excel, the data is
actually time data like the following:
2 days, 3 hours, 19 minutes, 15 seconds
17 hours, 4 minutes, 12 seconds
1 day, 1 minute, 38 seconds

it is all in column A, now i want to clean this data assigning it to
different columns, like in column B have hours, if no hours are found,
0 hours, C for minutes and so on so forth. i thought about Data > Text
to columns but it doesnt help me much because components will not be
aligned, should i do something to my data so i can be able to get the
preferred result?
and something more please, how could this be done if i wanted to use a
formula?
because i also tried text formulas and i failed, it would be
interesting to me to learn both way if it could be done by both, if
not, just something to proceed on my job and thanks in advance

 

[DocBrown]

Depending on how the source data is formatted, it could be very simple. If
the data is in CSV format, (comma seperated values). then just go to the
menu:
Data->Import external data..-> Import data.

Select 'Delimited'. Follow the prompts.

The csv data in the case of 'missing data' would look something like this

2 days, 3 hours, 19 minutes, 15 seconds
,17 hours, 4 minutes, 12 seconds
1 day, , 1 minute, 38 seconds

Notice the commas for the missing data. Excel will handle this type data
easily.
If the data is fixed width, Excel will handle that too. I can't demonstrate
that here.

John

 

[Totti]

Hi John,
the data is formatted with a ", " comma and space after each unit but
they are not aligned, i tried what you told me, it still gives me the
days and the hours on the same column. is there at least a formula i
can split the data in different columns?
Thanks

 

[David Biddulph]

A very similar question was asked less than two weeks ago, and you could use
a variation on the answer given then.

My approach would be to split the column into multiple columns using Data/
Text to Columns with comma and space as separators. This would leave your
numbers in the odd numbered columns, followed by the units in the even
numbered columns.
You could then use something like
=A1*(LEFT(B1,3)="day")+C1*(LEFT(D1,3)="day")+E1*(LEFT(F1,3)="day")+G1*(LEFT(H1,3)="day")
for your days
=A1*(LEFT(B1,4)="hour")+C1*(LEFT(D1,4)="hour")+E1*(LEFT(F1,4)="hour")+G1*(LEFT(H1,4)="hour")
for your hours, and so on.

 

[Totti]

1000 thanks David, it works great