Text to Numbers
 

Hi everyone. I have a column of data imported into excel that has
text and numbers. Such as 3 hours, 57 minutes, 43 seconds. It might
also just have 39 seconds or 1 day, 4 hours, 45 minutes. It would
look like this:

A1
3 hours, 57 minutes, 43 seconds
39 seconds
1 day, 4 hours, 45 minutes


I need to take this column and convert each entry to seconds. f
anyone can think of a way to do this it would be greatly appreciated.

Text to Numbers
 

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*IF(LEFT(B1,3)="day",24*60*60,IF(LEFT(B1,4)="ho ur",3600,IF(LEFT(B1,6)="minute",60,IF(LEFT(B1,6)=" second",1,IF(AND(A1="",B1=""),0,"error"))))) for the first column, and add similar values for 3rd, 5th, and however manyother columns you need.--David Biddulph"clk" wrote in ... Hi everyone. I have a column of data imported into excel that has text and numbers. Such as 3 hours, 57 minutes, 43 seconds. It might also just have 39 seconds or 1 day, 4 hours, 45 minutes. It would look like this: A1 3 hours, 57 minutes, 43 seconds 39 seconds 1 day, 4 hours, 45 minutes I need to take this column and convert each entry to seconds. f anyone can think of a way to do this it would be greatly appreciated.

Text to Numbers
 

Try this small UDF:

Function seconds(r As Range) As Long
labell = Array("second", "minute", "hour", "day")
faktor = Array(1, 60, 3600, 86400)
seconds = 0
v = r.Value
ary = Split(v, ", ")
For i = LBound(ary) To UBound(ary)
parts = Split(ary(i), " ")
For j = 0 To 3
If InStr(parts(1), labell(j)) 0 Then
seconds = seconds + parts(0) * faktor(j)
End If
Next
Next
End Function


So if A1 contains:

3 hours, 57 minutes, 43 seconds

the formula:
=seconds(A1) will display 14263

--
Gary''s Student - gsnu200812


"clk" wrote:

Hi everyone. I have a column of data imported into excel that has
text and numbers. Such as 3 hours, 57 minutes, 43 seconds. It might
also just have 39 seconds or 1 day, 4 hours, 45 minutes. It would
look like this:

A1
3 hours, 57 minutes, 43 seconds
39 seconds
1 day, 4 hours, 45 minutes


I need to take this column and convert each entry to seconds. f
anyone can think of a way to do this it would be greatly appreciated.

Text to Numbers
 

On Nov 5, 1:39*pm, Gary''s Student
wrote:
Try this small UDF:

Function seconds(r As Range) As Long
labell = Array("second", "minute", "hour", "day")
faktor = Array(1, 60, 3600, 86400)
seconds = 0
v = r.Value
ary = Split(v, ", ")
For i = LBound(ary) To UBound(ary)
* * parts = Split(ary(i), " ")
* * For j = 0 To 3
* * * * If InStr(parts(1), labell(j)) 0 Then
* * * * * * seconds = seconds + parts(0) * faktor(j)
* * * * End If
* * Next
Next
End Function

So if A1 contains:

3 hours, 57 minutes, 43 seconds

the formula:
=seconds(A1) will display 14263

--
Gary''s Student - gsnu200812



"clk" wrote:
Hi everyone. *I have a column of data imported into excel that has
text and numbers. *Such as 3 hours, 57 minutes, 43 seconds. *It might
also just have 39 seconds or 1 day, 4 hours, 45 minutes. *It would
look like this:

A1
3 hours, 57 minutes, 43 seconds
39 seconds
1 day, 4 hours, 45 minutes

I need to take this column and convert each entry to seconds. *f
anyone can think of a way to do this it would be greatly appreciated.- Hide quoted text -

- Show quoted text -

Thank you so much for the ideas. I used the option of splitting the
columns up and using the formula. Worked great! Thanks again.