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
