How do I use regular expression to extract a value with specific unit and sum up

Question

How do I use a regular expression to extract a value with specific unit and sum up.

Input at A1, output at A2

I only want to add the value with mm

I tried this code provided from stackoverflow, and I add "?mm" after the (\d+(?:\.\d+)?)but there is an error message.

Function sumNums(str As String) As Double
Dim n As Long
Static rgx As Object, cmat As Object

If rgx Is Nothing Then
    Set rgx = CreateObject("VBScript.RegExp")
End If

With rgx
    .Global = True
    .MultiLine = True
    .Pattern = "(\d+(?:\.\d+)?)?mm"
    If .test(str) Then
        Set cmat = .Execute(str)
        For n = 0 To cmat.Count - 1
            sumNums = sumNums + CDbl(cmat.Item(n))
        Next n
    End If
End With
End Function

The Regular Expression looks right. Can you please post the error message? — 4ndy, Oct 22 '18 at 04:36
Have you added a reference to Regular Expressions? [Have a look at the accepted answer under this question](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). — 4ndy, Oct 22 '18 at 05:44
@4ndy Hi, it works for me for this code "(\d+(?:\.\d+)?)" ---> it will sum up all the number no matter what. but it didnt work at this code "(\d+(?:\.\d+)?)?mm". it will show up #Value! . — Hsu.George, Oct 22 '18 at 05:49

score 2 · Accepted Answer · 2018-10-22T05:58:23.690

The strings being parsed by the regex are just that; strings. The original UDF uses CDbl to convert these to true numbers and it is successful because they are only numbers. To convert a string-number with a trailing suffix, use Val.

Try this simple regex based UDF.

Option Explicit


Function sumMMnums(str As String) As Double
    Dim n As Long
    Static rgx As Object, cmat As Object

    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    End If

    With rgx
        .Global = True
        .MultiLine = True
        .Pattern = "(\-?\d*\.?\d+)mm"
        If .test(str) Then
            Set cmat = .Execute(str)
            For n = 0 To cmat.Count - 1
                sumMMnums = sumMMnums + Val(cmat.Item(n))
            Next n
        End If
    End With
End Function

Finally... thanks Jeeped. Because you answered the same question from me before.. that's why.... — Hsu.George, Oct 22 '18 at 05:56

How do I use regular expression to extract a value with specific unit and sum up

1 Answers1