VBA excel for finding digit pattern in the string

Question

I was able to write a code which finds 6 digit numbers within the string and copy it into next column but I would like to add search which also can find numbers with pattern ##-#### and copy it into next column, example below:

Can somebody help me?

Sub Pull_6_Digit_Numbers_From_String()


   Dim r As Range, i As Long
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\b\d{6}\b"
        For Each r In Range("A1", Range("A" & Rows.Count).End(xlUp))
            If .test(r.Value) Then
                For i = 0 To .Execute(r.Value).Count - 1
                    r(, i + 2).Value = .Execute(r.Value)(i)
                Next
            End If
        Next
    End With

Answer 1

Your current pattern, \b\d{6}\b, matches a word boundary, then 6 consecutive digits and then again a word boundary. So, it matches 123456 in text 123456 here. To also match 12-3456, you may split the pattern into 2- and 4-digit subpatterns and insert an optional - pattern, -?.

\b\d{2}-?\d{4}\b

See the regex demo

The -? matches 1 or 0 hyphens, and \d{2} with \d{4} will match 6 digits between word boundaries all in all.