Text to Numbers

[clk]

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.

 

[David Biddulph]

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)="hour",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" <[email protected]> wrote in messageHi 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.>

 

[Gary''s Student]

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

 

[clk]

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







- 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.