Data Cleansing Challenge: Convert Text to Time Values

Brute force always works for me…. 🙂

=VALUE(TEXT(IFERROR(LEFT([@Time],FIND(“h”,[@Time],1)-2),”0″)&”:”&IFERROR(MID([@Time],FIND(“m”,[@Time],1)-3,2),”00″)&”:”&IFERROR(MID([@Time],FIND(“c”,[@Time],1)-5,2),”00″),”h:mm:ss”))

Nice one, you are genius!!!

Glad I am not the only one, 😉
Jerry’s way, take 2
i.e.
don’t force it, get a bigger hammer

=TIME(IFERROR(TRIM(MID([@Time];MAX(1;FIND(” h”;[@Time])-2);2));0);IFERROR(TRIM(MID([@Time];MAX(1;FIND(” m”;[@Time])-2);2));0);IFERROR(TRIM(MID([@Time];MAX(1;FIND(” s”;[@Time])-2);2));0))

As it turned out my proposal had slight variations in columns D:F as compared to ‘Solution’ sheet.
D: =IFERROR(VALUE(MID(C2,SEARCH(D$1,C2)-(SEARCH(D$1,C2)-1),2)),0)
E: =IFERROR(VALUE(MID(C2,SEARCH(E$1,C2)-(MIN(SEARCH(E$1,C2)-1,3)),2)),0)
F: =IFERROR(VALUE(MID(C2,SEARCH(F$1,C2)-3,2)),0)
Formula for column G was the same.
I hope that you have a blessed day.

VBA brute force:

Option Explicit
Function GetTime(ByVal s As String) As Double
GetTime = Evaluate(Replace(Replace(Replace(Replace(Replace(s, ” hour”, “*1/24″), ” minute”, “*1/24/60″), ” second”, “*1/24/60/60”), “s”, “”), ” “, “+”))
End Function

Just convert the string into a calculation and evaluate it…
Nifty detail: make sure singular and plural of “hour”, etc. are both treated.

I was able to complete the challenge using text to columns and 3 nested IF statements.

Here is my video solution using text to columns and nested IF statements
https://youtu.be/D9FtYZaSUnM

I used this formula:

=TIME(
IFERROR(IF(SEARCH(“hour”,[@Time],1)=4,MID([@Time],SEARCH(“hour”,[@Time],1)-3,2),MID([@Time],SEARCH(“hour”,[@Time],1)-2,2)),0),
IFERROR(IF(SEARCH(“minute”,[@Time],1)=3,MID([@Time],SEARCH(“minute”,[@Time],1)-2,2),MID([@Time],SEARCH(“minute”,[@Time],1)-3,2)),0),
IFERROR(MID([@Time],SEARCH(“second”,[@Time],1)-3,2),0))

It looks better when separated by enter breaks.

The other not so elegant, but fast solution would be using Find And Replace to change all “hours”,”minutes”, and “seconds” to “:”

I used helper columns to find the “Hour”, “Minute”, “Second” and find where the beginning respective number is and if not present, =0. I then used Time function to convert to time.

I used search and Mid too.

Not as difficult as I thought.

I produced a UDF to add to my personal workbook. I also wrote it so that it could be easily adapted to used for other data types e.g. 5 years 6 months 2 days

It’s a bit messy and I may have to tidy up all the if statements, but it seems to work.

Option Explicit

Const DATA_STRING_HOUR = “Hour”
Const DATA_STRING_MINUTE = “Minute”
Const DATA_STRING_SECOND = “Second”

Private Function Extract(ByVal strMyTime As String, strTimePart As String) As Long

strMyTime = LCase(strMyTime)
strTimePart = LCase(strTimePart)

Dim lngPosStart As Long
Dim lngPosEnd As Long
lngPosEnd = InStr(1, strMyTime, strTimePart)
If lngPosEnd 0 Then
lngPosStart = InStrRev(strMyTime, ” “, lngPosEnd – 2)
Else
lngPosStart = 1
End If

If lngPosStart = 0 Then
lngPosStart = 1
End If

If lngPosEnd 0 Then
Extract = CLng(Trim(Mid(strMyTime, lngPosStart, lngPosEnd – lngPosStart)))
End If

End Function
Function ExtractTime(ByVal strMyTime As String) As Date

Dim lngHour As Long
Dim lngMinute As Long
Dim lngSecond As Long

lngHour = Extract(strMyTime, DATA_STRING_HOUR)
lngMinute = Extract(strMyTime, DATA_STRING_MINUTE)
lngSecond = Extract(strMyTime, DATA_STRING_SECOND)

ExtractTime = TimeSerial(lngHour, lngMinute, lngSecond)

End Function

I went old school. Converted text into columns using ‘space’ as Delimiter, sorted first word column for hours, inserted cells to shift non Hour entries left, sorted next word column for Seconds, inserted cells to shift non Seconds entries to the left.
End result = 6 columns:
[XXX] [Hours] [XXX] [Minutes] [XXX] [Seconds]

Time formula then combined data into one time

Custom number format applied

Done.

Hi,
it seems the format is not fixed x hour(s) y minute(s) z second(s) but if the numerical values are 0, that part of the format does not appear.
Meaning the formulation of the problem is not entirely correct.
Because otherwise you would expect in row 3 this :
0 hours 0 minutes 25 seconds
for example
Otherwise I would have gone for the replacement approach as well. Replacing the spaces with colons is nice to then be able to have Excel interpret it as time.

I used Power Query also, but was unable to figure out how to do it without custom M code.

hours if column C contains “hour” then extract the first 2 digits, otherwise return “00”
=IF(ISNUMBER(SEARCH(“hour*”,[@Time])),LEFT([@Time],2),”00″)
minutes find “m” in column C, move back 3 spaces and extract 2 digits; wrapped in an ‘IFERROR’ to return “00”
=IFERROR(MID([@Time],FIND(“m”,[@Time])-3,2),”00″)
secs find “sec” in column C, move back 3 spaces and extract 2 digits; wrapped in a ‘IFERROR’ to return “00”
=IFERROR(MID([@Time],FIND(“sec”,[@Time])-3,2),”00″)
duration textjoin the three columns with “:” as the delimiter, wrapped in a TEXT function formatted as HH:MM:ss
=TEXT(TEXTJOIN(“:”,TRUE,[@Hours],[@Min],[@Sec]),”HH:MM:ss”)

I have updated my solution because when I was extracting the minutes I did not allow for cells that contained no hours and only single digit minutes.
hours: if column C contains “hour” then extract the first 2 digits, otherwise return “00”
formula: =IF(ISNUMBER(SEARCH(“hour*”,[@Time])),LEFT([@Time],2),”00″)

minutes: if the string “hour” does not exist in column C then extract the first two digits, else find “m”, move back 3 spaces and extract 2 digits
formula: =IF(NOT(ISNUMBER(SEARCH(“hour”,@Time]))),LEFT([@Time],2),MID([@Time],FIND(“m”,[@Time])-3,2))

secs: find “sec” in column C, move back 3 spaces and extract 2 digits; wrapped in a ‘IFERROR’ to return “00”
formula: =IFERROR(MID([@Time],FIND(“sec”,[@Time])-3,2),”00″)

duration: textjoin the three columns with “:” as the delimiter, wrapped in a TEXT function formatted as HH:MM:ss
formula: =TEXT(TEXTJOIN(“:”,TRUE,[@Hours],[@Min],[@Sec]),”HH:MM:ss”)

Select sheet “Raw Data”
Run Macro SplitTextToTime
———————————————-
Sub SplitTextToTime()
Dim objRange1 As Range
Set objRange1 = Range(“C:C”)
” Clean everything after column C
Columns(“D:Z”).Select
Selection.Delete Shift:=xlToLeft
Range(“A1″).Select
” Headings
”Sheets(“xxxxxxxx”).Select
Range(“J1”).Select
ActiveCell.FormulaR1C1 = “Hours”
Range(“K1”).Select
ActiveCell.FormulaR1C1 = “Minutes”
Range(“L1”).Select
ActiveCell.FormulaR1C1 = “Seconds”
Range(“M1”).Select
ActiveCell.FormulaR1C1 = “Time”
Range(“J1:M1”).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.Font.Bold = True
End With
Range(“A1″).Select
”SplitText
objRange1.TextToColumns _
Destination:=Range(“D1″), _
DataType:=xlDelimited, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True
”PlaceFormulas
[J2].Formula = “=IF(LEFT(E2,1)=””h””,D2,0)”
[K2].Formula = “=IF(LEFT(E2,1)=””m””,D2,0)+IF(LEFT(G2,1)=””m””,F2,0)”
[L2].Formula = “=IF(LEFT(E2,1)=””s””,D2,0)+IF(LEFT(G2,1)=””s””,F2,0)+IF(LEFT(I2,1)=””s””,H2,0)”
[M2].Formula = “=TIME(J2,K2,L2)”
Columns(“M:M”).Select
Selection.NumberFormat = “h:mm:ss”
Range(“M1″).Select
”CopyDownFormula
Dim Lastrow As Long
Lastrow = Range(“A1”).CurrentRegion.Rows.Count
Range(“J2:M” & Lastrow).FillDown
”OnlyValues
”Sheets(“xxxxxxx”).Select
Columns(“J:M”).Select
Selection.Copy
Range(“J1”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns(“D:I”).Select
Range(“I1”).Activate
Selection.Delete Shift:=xlToLeft
Range(“A1”).Select
End Sub

Hi Jon
I built this out in three separate columns (for testing and sanity), then combined them into one, and added the VALUE function to derive the time. C3 looks like this:
=VALUE(IF(ISERR(FIND(” hours”,C2)),”0:”,LEFT(C2,FIND(” hours”,C2)-1)&”:”) & IF(ISERR(FIND(” minutes”,C2)),”00:”,MID(C2,FIND(” minutes”,C2)-2,2)&”:”) & IF(ISERR(FIND(” seconds”,C2)),”00″,MID(C2,FIND(” seconds”,C2)-2,2)))

For really long data sets, this may get slow but at the size of the example it works pretty well. Converting this logic to VBA as a function would probably be better as the data sets get bigger.

=IFERROR(MID($B$6,SEARCH(“hours”,$B$6,1)-3,2),”00″)&”:”&IFERROR(MID($B$6,SEARCH(“minutes”,$B$6,1)-3,2),”00″)&”:”&IFERROR(MID($B$6,SEARCH(“seconds”,$B$6,1)-3,2),”00″)

this formula will produce the correct time format ie HH:MM:SS no matter what order the data is

Sorry people
i misinterpreted the challenge
My time, hrs, mins and sec, were all in same cell (C7)
Eg 11 hours 30 minutes 20 seconds

with the time in one cell, the below formula works

=IFERROR(MID($C$7,SEARCH(“hours”,$C$7,1)-3,2),”00″)&”:”&IFERROR(MID($C$7,SEARCH(“minutes”,$C$7,1)-3,2),”00″)&”:”&IFERROR(MID($C$7,SEARCH(“seconds”,$C$7,1)-3,2),”00″)