vba - Convert Excel Text to Time

Question

I have to convert some formatted text to excel time, but I could not find a solution by myself.

Here are some examples of the input text and how it should be converted:

 - 1 Hour 14 minutes    ==> 01:14:00
 - 1 minute.            ==> 00:01:00
 - 1 Hour 1 minute      ==> 01:01:00
 - 2 minutes            ==> 00:02:00
 - 3 minutes 12 seconds ==> 00:03:12
 - 29 seconds           ==> 00:00:29

Observe that some times there are both minutes and seconds and some others only one of minutes/seconds, besides some times you find minutes (plural) and some others just minute (singular). Finally, some punctuation signs could be in the text some times.

The data is in a spreadsheet column and I want to extract the excel formatted time in a different column on the spreadsheet.

I've tried different versions of TimeValue() and DateValue() and some nested replace() all of them in a cell formula, but none of them worked for all cases.

Could you give me an idea or some advice on how to approach this problem?

Thanks,

Paul

Answer 1

I played with your question for a day and see that others have done the same. I admire the solution offered by Ron Rosenfeld using FilterXML which I briefly considered and discarded as too outlandish. It isn't. But neitehr is it neat. For "neat" look no further than the UDF below. Call it from the worksheet with something like =TextToTime(A1) and be sure to format the cell you put this in as Time, perhaps like hh:mm:ss.

Function TextToTime(Cell As Range) As Double

    Dim Fun(2) As String
    Dim Txt() As String
    Dim Tmp As Variant
    Dim i As Integer

    Txt = Split(Cell.Value)
        For i = 1 To UBound(Txt)
            If Not IsNumeric(Txt(i)) Then
                Tmp = 0
                Tmp = InStr("HMS", UCase(Left(Trim(Txt(i)), 1)))
                If Tmp Then
                    Fun(Tmp - 1) = Val(Txt(i - 1))
                End If
            End If
        Next i

    For i = LBound(Fun) To UBound(Fun)
        If Fun(i) = "" Then Fun(i) = 0
    Next i

    TextToTime = TimeValue(Join(Fun, ":"))
End Function

This function is very versatile. It can translate strings like "5 hours 10 minutes 15 seconds" or "5 hours, 10 minutes, 15 seconds" (with commas) or even "5 hours & 10 minutes and 15 seconds". It needs the spaces around the numbers but doesn't balk at extra spaces or typos. It fails on "75 minutes and 66 seconds" but that could be dealt with if it's an issue. The key advantage of VBA is that almost anything can be dealt with using just a few extra lines of code. Worksheeet functions don't have that capability.