Define multiple phone number patterns to one RegEx.

[aRush113]

Good day everyone
I was wondering if it's possible to define multiple patterns to one RegEx.
I need to extract a phone number from a TextBox string in a UserForm but the number could have different patterns like
.Pattern = "([0-9]{2} [0-9]{4} [0-9]{6})"
or
.Pattern = "([0-9]{3}-[0-9]{3}-[0-9]{4})"
and so on...

The text string itself is composed of:
A client name, an address, an email, a price a PHONE NUMBER (not always formatted the same way, thus the reason i need multiple patterns) and other text, and not always in this order.

This is just part of the script i'm using which works fine for ONE pattern

    If Not IsNull(sInput) Then
        Set oRegEx = CreateObject("VBScript.RegExp")
        With oRegEx
            .Pattern = "([0-9]{2} [0-9]{4} [0-9]{6})" ' Tried using "([0-9]{2} [0-9]{4} [0-9]{6})" or "([0-9]{3}-[0-9]{3}-[0-9]{4})" but obviously didn't work.
            .Global = True
            .IgnoreCase = True
            .MultiLine = True
            Set oMatches = .Execute(sInput)
        End With
        For Each oMatch In oMatches
            sPhoneNo = oMatch.value & "," & sPhoneNo
        Next oMatch
        If Right(sPhoneNo, 1) = "," Then sPhoneNo = Left(sPhoneNo, Len(sPhoneNo) - 1)
        ExtractPhoneNo = Split(sPhoneNo, ",")    'Return an array of phone numbers extracted from sInput
    Else
        ExtractPhoneNo = Null
    End If

Thanx in advance.

 

[JEC]

Can you post sample phone numbers? If you can show all possible formats, one regex string would be possible probably

quick and dirty

([0-9]{2} [0-9]{4} [0-9]{6})|([0-9]{3}-[0-9]{3}-[0-9]{4})

 

[aRush113]

Thanx for replying JEC

For completeness my text string would look something like this:

Set ws = TextBox1
ws.value = "expedia (id: 353349353352355355389) Date 15-8 2nt 4px John Doe (7tj State, City, Country) 570 Confermed Phone1 +XX XXXX XXXXXX Tel.2 XXX-XXX-XXXX email: john.doe@gmail.com "
aPhoneNos = ExtractPhoneNo(ws.value)

Some (or most) possible patterns would be:
+XX XXXX XXXXXX
XXX-XXX-XXXX
+XXX XX XX XXX
XXX XX XX XXX

I tried your pattern but it only works with the first pattern.

 

[JEC]

check this out (it catches all 4 patterns), assuming you don't care about the "+" sign

Sub jec()
 Dim vStr, sPhoneNo, it
 vStr = "expedia (id: 353349353352355355389) Phone1 +55 5555 555555 Tel.2 555-555-5555 email: john.doe@gmail.com 555 55 55 555 blablablablalba +555 55 55 555"

 With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([0-9]{2,3})( |-)([0-9]{2,4})( |-)([0-9]{4,6}|[0-9]{2} [0-9]{3})"
    If .test(vStr) Then
      For Each it In .Execute(vStr)
        sPhoneNo = sPhoneNo & IIf(Len(sPhoneNo), ", ", "") & it
      Next
      MsgBox sPhoneNo
    End If
 End With
End Sub

 

[aRush113]

WOW, Thanx M8, it works like a charm.

 

[JEC]

You're welcome! Cheers