excel - Regex return seven digit number match only

Question

I've been trying to build a regular expression to extract a 7 digit number from a string but having difficulty getting the pattern correct.

Example string - WO1519641 WO1528113TB WO1530212 TB

Example return - 1519641, 1528113, 1530212

My code I'm using in Excel is...

Private Sub Extract7Digits()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A300")

    For Each c In Myrange
        strPattern = "D(d{7})D"
        'strPattern = "(?:D)(d{7})(?:D)"
        'strPattern = "(d{7}(Dd{7}D))"

        strInput = c.Value

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            Set matches = regEx.Execute(strInput)
            For Each Match In matches
                s = s & " Word: " & Match.Value & " "
            Next
                c.Offset(0, 1) = s
            Else
                s = ""
        End If

    Next
End Sub

I've tried all 3 patterns in that code but I end up getting a return of O1519641, O1528113T, O1530212 when using "D(d{7})D". As I understand now the () doesn't mean anything because of the way I am storing the matches while I initially thought they meant that the expression would return what was inside the ().

I've been testing things on http://regexr.com/ but I'm still unsure of how to get it to allow the number to be inside the string as WO1528113TB is but only return the numbers. Do I need to run a RegEx on the returned value of the RegEx to exclude the letters the second time around?

Answer 1

I suggest using the following pattern:

strPattern = "(?:^|D)(d{7})(?!d)"

Then, you will be able to access capturing group #1 contents (i.e. the text captured with the (d{7}) part of the regex) via match.SubMatches(0), and then you may check which value is the largest.

Pattern details:

• (?:^|D) - a non-capturing group (does not create any submatch) matching the start of string (^) or a non-digit (D)
• (d{7}) - Capturing group 1 matching 7 digits
• (?!d) - a negative lookahead failing the match if there is a digit immediately after the 7 digits.