extracting a set of numbers with regex

[elefantsko]

Hi! I need some help with regex.. I'm trying to find a solution to extract a set of numbers from a long text. The text might look like:
"Blablabla bla bla blablabla 2 blabla bla 44 blablabla 1234567-0100 bla bla blabla"

What I need to extract from that text is "1234567-0100" (with the - ).

This is what i got:

Sub simpleRegex()    Dim strPattern As String: strPattern = "[0-9]{7}\-[0-9]{4}"
    Dim returnString As String: returnString = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range


    Set Myrange = ThisWorkbook.Worksheets(1).Range("A1")


    If strPattern <> "" Then
        strInput = Myrange.Value


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


        If regEx.Test(strInput) Then
            MsgBox (regEx.Replace(strInput, returnString))
        Else
            MsgBox ("Not matched")
        End If
    End If
End Sub

but this returns everything before the set of numbers...

Anyone?

Regards!

 

[Mike LH]

Hi,

Your pattern is fine, try this one.

Sub simpleRegex()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim MyRange As Range, Outstring As String
    Set RegExp = CreateObject("vbscript.RegExp")
    With RegExp
        .Global = True
        .Pattern = "[0-9]{7}\-[0-9]{4}"
    End With
    Set MyRange = ThisWorkbook.Worksheets(1).Range("A1") 'change to suit
    
        Outstring = ""
        Set Collection = RegExp.Execute(MyRange.Value)
        For Each RegMatch In Collection
            Outstring = Outstring & RegMatch
        Next
        If Outstring <> "" Then
            MsgBox Outstring
        Else
            MsgBox "Not MAtched"
        End If
End Sub

 

[elefantsko]

Thank you so much mike!! Works like a charm