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

Do you mean you want to extend your current pattern? Try \b\d{2}-?\d{4}\b — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Aug 17, 2017 at 14:12

Wiktor Stribiżew · Accepted Answer · 2017-08-17 14:18:40Z

4

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.

answered Aug 17, 2017 at 14:18

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

FotoDJ Over a year ago

Got it, Thank you Wiktor for clear and transparent explanation!

FotoDJ Over a year ago

Wiktor, is there a way to pull the text with starts with capital N and will have combination of 3 to 6 letters or numbers with it ie. N24 or NX747 or N35655V but it should ignore N2 or N8(N plus 1 character) or N1234567(N plus more than 6 characters?

Wiktor Stribiżew Over a year ago

@FotoDJ: It seems you want either \bN[^\W_]{3,6}\b or \bN[^\W_]{2,6}\b.

FotoDJ Over a year ago

\bN[^\W_]{3,6}\b solves the problem, thanks a lot! it matches also N and lower case words like North, New etc, Is there a way to prevent matching lower case after N which would eliminate above matching problem, I promise this is the last question, you 've been a great help, Wiktor.

FotoDJ Over a year ago

It is brilliant again.THANK YOU.

|

Collectives™ on Stack Overflow

VBA excel for finding digit pattern in the string

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related