VBA Regular Expression to return ALL matching patterns

0 votes

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 (based on around the same basic script found here - https://www.mrexcel.com/board/threads/udf-to-extract-phone-numbers-from-cell-content.1045305/) 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
Mar 24, 2023 in Others by narikkadan
• 63,420 points

1 answer to this question.

0 votes
You have set the global to false, which meant that only the first match was found..
answered Mar 24, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Regular expression to match standard 10 digit phone number

 If you are also interested in matching ...READ MORE

answered Feb 22, 2022 in Others by Aditya
• 7,680 points
0 votes
1 answer

How to return a result from a VBA function

You must associate the value with the ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Excel VBA: Trying to read all files in folder based on cell input and output to another cell

Your array has no capacity for data ...READ MORE

answered Jan 24, 2023 in Others by narikkadan
• 63,420 points
+2 votes
0 answers

Unable to return data to main function from promise

Hi, i am fairly new to node ...READ MORE

Sep 6, 2019 in Others by Muhammad Ahmed
• 140 points
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

How to make an error flagging array in VBA and translate all array elements as a string message?

In my opinion, using an array in ...READ MORE

answered Mar 17, 2023 in Others by Kithuzzz
• 38,010 points
0 votes
1 answer

VBA to protect and unprotect in given range in sheet

It needs a minor tweak to become ...READ MORE

answered Nov 24, 2022 in Others by Kithuzzz
• 38,010 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP