Trying to extract valid phone numbers from a string with no visible delimiters between different data types (only the 1st phone number is to be used, so I used global=false in the code below). In effect, the data around the potential phone number is random and irrelevant, e.g. [random garbage][optional '1'][optional '(']###[optional')'[[random or no white space]###-####[random garbage] or [random garbage]###[optional '-']###,[optional '-']####[random garbage]. I could make it even more robust, but I've examined the data and this should be enough. Here's a code snippet from a function (it returns the matching phone number) that contains the pattern that worked until I realized that I also need to search for an extension next to the phone number (i.e. [phone number][white space}x#) and if that exists, to treat the phone number as invalid (.test should evaluate to false)
With regex
.Global = False
.Pattern = "1?(\(?\d{3}\)?\(s+)?|\d{3}-?)\d{3}-?\d{4}"
'this works but does detect an extension as explained above
End With
I tried the following patterns and they all failed (even valid phone numbers had .test evaluate to false):
.Pattern = "1?(\(?\d{3}\)?\(s+)?|\d{3}-?)\d{3}-?\d{4}^(\s?x\d)"
'detect not([optional single white space]x#), added "^(\s?x\d)"
'or
.Pattern = "1?(\(?\d{3}\)?\(s+)?|\d{3}-?)\d{3}-?\d{4}^((\s+?)[x]\d)"
'detect not([optional multiple white space]x#), added "^((\s+?)[x]\d)"
Not sure how to combine positive match tests and negative (not) match tests in the same pattern. When I couldn't get it to work, I tried the following Like patterns (using VBA 'Like', prior to calling the function that utilized Regexp) and that also failed (all evaluated to false even with test strings that contained examples such as "...1x2" or "5 x33" or "...7 x444": "#x#", "#{ x}#", ""#{ x}#". Here is the code snippet to test the Like function:
If Not (OrigNum Like "*#x#" Or OrigNum Like "*#[ x}#" Or OrigNum Like "*#[ x]#*") Then
Debug.Print "No #x# in string"
OrigNum = ExtractPhoneNumber(OrigNum)
Else
Debug.Print "#x# in string"
End If
Every string evaluated ended up causing "No x# in string" to be displayed (evaluated to false), even when the string contained the above examples ("...1x2" or "5 x33" or "...7 x444", which should have evaluated to true and "#x# in string" being displayed.
Dazed and confused for so long it's...OK, enough of the Led Zepp reference :-)
Aucun commentaire:
Enregistrer un commentaire