Regular Expression Pattern

Hi all,

Using Excel 2010, 2013.

What would be the correct regular expression pattern to extract this substring : 234-456-00000000-45-234 from a string like

mnasdf 234 234-456-00000000-45-234 sdf 123 34

The string will vary, the substring does not vary other than the digits can be any number 0-9
I need to retain the hyphens "-" as well

I tried patterns like

.Pattern = "[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9]"
.Pattern = "/d{3}-/d{3}-/d{8}-/d{2}-/d{3}"

But not working

Thx
w

Sub Test()
    Const strTest As String = "234-456-00000000-45-234"
    Debug.Print RE6(strTest)
End Sub
 
Function RE6(strData As String) As String
    Dim RE As Object, REMatches As Object
     
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9]"
    End With
     
    Set REMatches = RE.Execute(strData)
    RE6 = REMatches(0)
     
End Function

EDITED TO USE JosephP's pattern (which is more concise)
Try this

Function FindPattern(ByVal text As String) As String

Dim result As String
Dim allMatches As Object
Dim RE As Object
result = "no match"

Set RE = CreateObject("vbscript.regexp")

' RE.Pattern = "[0-9]{3}-[0-9]{3}-[0-9]{8}-[0-9]{2}-[0-9]{3}" <-Commented out
RE.Pattern = "\d{3}-\d{3}-\d{8}-\d{2}-\d{3}"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(text)

If allMatches.Count <> 0 Then
    result = allMatches.item(0)
Else
    Set allMatches = RE.Execute(text)
    If allMatches.Count <> 0 Then
        result = allMatches.item(0)
    End If
End If

FindPattern = result
End Function

Applied this way...
A1: mnasdf 234 234-456-00000000-45-234 sdf 123 34
B1: =findpattern(A1)

In that example, the function returns: 234-456-00000000-45-234

Is that something you can work with?

perhaps

        .Pattern = "\d{3}-\d{3}-\d{8}-\d{2}-\d{3}"

Ron , JosephP -

Thanks for your help
I combined with some help I received earlier from shg and using these 2 functions I now have exactly what I need.

Thanks again,
w

Function GetRegEx() As Object
  On Error Resume Next
  Set GetRegEx = CreateObject("VBScript.RegExp")
End Function

Function GetCleanString(oRE As Object, strLoanNumber As String) As String
    Dim reMatches As Object

    With oRE
        .Pattern = "\d{2}-\d{4}-\d{15}-\d{1}"
        .Global = True
        .IgnoreCase = True
        Set reMatches = .Execute(strLoanNumber)
    End With
    
    GetCleanString = reMatches(0)

End Function