VBA Regular Expression to return ALL matching patterns

Question

I am currently trying to separate apart contact details that having been gathered - we should have one entry per contact method but people are mashing in numerous to one box. I have two UDF that pull out the first email address and phone number within the text string, however I would like to pull out ALL the strings that match the patterns.

I thought I could call matches(X).Value to draw the X hit but even when I have multiple values in a string my matches.count only returns 1.

So the string "02012345678 01234123456 FAKEMAIL@GMAIL.COM" will drop out 02012345678 but I would like to pull both numbers out, as well as being able to count them.

Be aware that anything may and has been input because the data is being entered into a simple text field. Although it will take time and training will only take us so far, we want to change the situation.

The full code I am using for phone numbers is below.

Private regexPhone As Object

Public Function GetPhoneNumberFromText(inputString As String) As String
    
    Dim matches As Object
    
    ' Set default value - blank in this case but could be "Not Found" or similar
    GetPhoneNumberFromText = ""
    
    ' Set up the regular expression first time around
    If regexPhone Is Nothing Then
        Set regexPhone = CreateObject("VBScript.RegExp")
        regexPhone.Pattern = "(?:\+[0-9]{3})?\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{5})"
        regexPhone.Global = False
        
        
    End If
    
    ' Get the matches
    Set matches = regexPhone.Execute(inputString)
    
    ' Quit if we can't find a number
    If matches.Count = 0 Then Exit Function
    
    ' Just process the first match
    'GetPhoneNumberFromText = regex.Replace(matches(0).Value, "$1$2$3")
    GetPhoneNumberFromText = matches(0).Value
    
    End Function

Answer 1

You have set the global to false, which meant that only the first match was found..