parse a string with VBA on EXCEL

Question

I'm trying to analyse a text file imported in an excel tab.

The first column is a text code used to represent the timeline of operations and I would tike to convert this text code into a number of seconds.

Text codes can be formated like this :

5,9h180s            ->  21420s
10min               ->  600s
3,4h5min30s         ->  12570s
...

My idea is to extract the values with a regex a use them later in a function, but I don't know VBA very good.

Is there a function in EXCEL VBA to perform regular expressions on a string and extract data from it ?

Do you have an example of a such function ?

I've reopened this question because I found it interesting and need a little practise on my regex patterns. I won't say that the profile in question had nothing to do with it. — user4039065
– user4039065, Commented Jan 12, 2016 at 0:29

Community · Accepted Answer · 2017-05-23 11:45:20Z

After a few attempts at the various combinations (and some of my own imagination) I decided that truncating the time value string at the first letter of the unit would allow me to use that h / m / s as the last character in the regex .Pattern property. This pre-regex prepping gave me the best results.

In a standard module code sheet as,

Function howManySeconds(strTM As String) As Long
    Dim s As Long, tmp As String
    Dim rgx As Object, cmat As Object
    Dim x As Long, vPTTRNs As Variant

    Set rgx = CreateObject("VBScript.RegExp")
    vPTTRNs = Array("[0-9,\.,\s]{1,9}×$", _
                    "h", 3600, "m", 60, "s", 1)

    With rgx
        .Global = True
        .IgnoreCase = True

        For x = LBound(vPTTRNs) + 1 To UBound(vPTTRNs) Step 2
            If CBool(InStr(1, LCase(strTM), vPTTRNs(x), vbTextCompare)) Then
                tmp = Replace(Replace(Replace(LCase(strTM), _
                                        "seconds", "s"), "secs", "s"), _
                                        Chr(44), Chr(46))
                tmp = Replace(Replace(Left(tmp, InStrRev(strTM, vPTTRNs(x), -1, vbTextCompare)), _
                                        Chr(44), Chr(46)), Chr(32), vbNullString)
                .Pattern = Replace(vPTTRNs(LBound(vPTTRNs)), Chr(215), vPTTRNs(x))
                Set cmat = .Execute(tmp)
                If CBool(cmat.Count) Then
                    s = s + CLng(CDbl(Replace(cmat.Item(0), vPTTRNs(x), vbNullString)) * vPTTRNs(x + 1))
                End If
            End If
        Next x

    End With

    howManySeconds = s

    Set rgx = Nothing
End Function

Use like any native worksheet function. In C2 as,

=howManySeconds(A2)

You should note that (by the right-alignment) those values are true numbers which can be totalled or otherwise mathematically manipulated. A custom number format mask of 0\s_) has been applied to grant them a displayed s as a unit.

_{See How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops for an excellent local reference on using Regular Expressions in VBA.}

Collectives™ on Stack Overflow

parse a string with VBA on EXCEL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related