1

I have a series of data that is outputted from an application in the following format.

8d 21h 33m  
30s  
6m 32s  
1m 40s  
54m 6s  
11m 32s  
5d 3h 34m  
30s  
7m 40s  
19h 57m 49s  
1h 36m 9s  
1m 3s  
5m 11s  
19m 39s

I'd like to show this as the number of seconds in the next column in excel. If I was doing this in php or python I could split the string on each letter (h & m) but I've no idea how to do this in excel. Could anyone point me in the right direction?

Improve this question

2 Answers 2

Reset to default
1

This function should work:

Public Function to_seconds(data As String)
    Dim LArray() As String
    LArray = Split(data)
    total_time = 0
    For i = 0 To UBound(LArray)
        m = LArray(i)
        If m <> "" Then
            q = Right(m, 1)
            tt = Left(m, Len(m) - 1)
            ta = 0
            Select Case q
                Case "d"
                    ta = 86400
                Case "h"
                    ta = 3600
                Case "m"
                    ta = 60
                Case "s"
                    ta = 1
            End Select
            If IsNumeric(tt) Then
                total_time = total_time + (Int(tt) * ta)
            End If
        End If
    Next i
    to_seconds = total_time
End Function

Open VBA /Macros with ALT+ F11 and under ThisWorkbook insert a new module. Paste the code on the right side.

If your data is in cell A1 then cell B1must be =to_seconds(A1).

Improve this answer
3
  • This is perfect thank you. I was happy to try and get there with a little help but now I've seen the solution it might have taken a bit longer then I thought.
    – Drifter104
    Commented Jan 21, 2016 at 12:42
  • As you know php and python surely you can understand what I've done and improve this function.
    – jcbermu
    Commented Jan 21, 2016 at 12:50
  • I don't think I can claim to know php and python. More I can do certain things such as in this case splitting strings. I certainly don't know enough to optimise a given piece of code
    – Drifter104
    Commented Jan 21, 2016 at 13:44
1

This is basically the non-VBA version of jcbermu's answer. Open up the Name Manager and create a name with this formula, where A1 is the first row of your data.

=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A1,"d","*86400+"),"h","*3600+"),"m","*60+"),"s","*1+")&"0")

Give it a name, for example eval and type =eval in to a cell and autofill down.

Evaluate is a hidden function in Excel that only works when it's defined in the name manager. Simply typing the formula into a cell normally does not work.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .