Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |