Convert a cell containing time into correct format

[Gaetan321]

Hello.

I've been googling around for a few hours and searched the forum for an answer.

I have cells containing text "14 hr 05 min 21 sec" and "4 min 20 sec" which I'd like to convert into decimal seconds and I'm looking into the formula but can't find one

Much appreciated
Regards
G

 

[Yongle]

here's another for you

With values in column A starting at A2

Sub TimeIt()
    Dim cel As Range, a As String, b As Variant
    For Each cel In ActiveSheet.Range("A2", ActiveSheet.Range("A" & Rows.Count).End(xlUp))
        On Error Resume Next
        a = LCase(cel)
        If Not InStr(cel, "hr") > 0 Then a = "0 hr " & cel Else a = cel
        a = Replace(Replace(Replace(Replace(a, "hr", "@"), "min", "@"), "sec", ""), " ", "")
        b = Split(a, "@")
        cel.Offset(, 1) = b(0) * 3600 + b(1) * 60 + b(2)
    Next cel
End Sub

 

[CountTepes]

It just goes to show, that there are multiple ways to remove a feline dermis.