Reply to comment

Fixing Phone Numbers so they Fit a Common Format

It's common to get a list of names and phone numbers in a spreadsheet or from the web, and the formatting varies. In the US, people don't use a standard formatting consistently. Lately, they have taken to making phone numbers look like domain names or ip addresses, example: 415.555.1212. This function normalizes phone numbers to look like this: 213-555-1212 x1234. The code's structured so multiple regexes are used to perform the matching, allowing for easier modification of the code. (This code was written in Excel, but should work in any VBA application.) ~~~~ ' Convert almost any phone-like string into a normalized form. ' The form is AAA-EEE-NNNN xPBXX ' This works only for US telephone numbers, but it's structured so ' it's not too hard to alter for other formats (or other idiosyncratic ' data entry persons). ' Requires Microsoft VBScript Regular Expressions 5.5 Function NormalTel(Phone As String, Optional areacode As String) As String Dim parts(4) As String Dim re As RegExp Dim mat As MatchCollection Dim phAreacode As String Dim phExchange As String Dim phNumber As String Dim phExtension As String Phone = RTrim(Phone) Phone = Replace(Phone, Chr(160), " ") ' replace nbsp with regular space ' no areacodes '123-4567 Set re = New RegExp re.Pattern = "^(\d\d\d)[ .-](\d\d\d\d)[.,]*$" Set mat = re.Execute(Phone) If mat.Count > 0 Then If (areacode <> "") Then phAreacode = areacode Else phAreacode = "213" End If phExchange = mat(0).SubMatches(1) phNumber = mat(0).SubMatches(2) phExtension = "" End If '123-4567x12345 re.Pattern = "^(\d\d\d)[ .-]*(\d\d\d\d)\s*x(\d+)[.,]*$" Set mat = re.Execute(Phone) If mat.Count > 0 Then If (areacode <> "") Then phAreacode = areacode Else phAreacode = "213" End If phExchange = mat(0).SubMatches(1) phNumber = mat(0).SubMatches(2) phExtension = "" End If ' no pbx extensions '(123) 456-1234 re.Pattern = "^\((\d\d\d)\)[ ]*(\d\d\d)[ .-](\d\d\d\d)[.,]*$" Set mat = re.Execute(Phone) If mat.Count > 0 Then phAreacode = mat(0).SubMatches(0) phExchange = mat(0).SubMatches(1) phNumber = mat(0).SubMatches(2) phExtension = "" End If '123-456-1234 re.Pattern = "^(\d\d\d)[.-](\d\d\d)[ .-](\d\d\d\d)[.,]*$" Set mat = re.Execute(Phone) If mat.Count > 0 Then phAreacode = mat(0).SubMatches(0) phExchange = mat(0).SubMatches(1) phNumber = mat(0).SubMatches(2) phExtension = "" End If ' with pbx extensions '(123) 123-1234 x1234 re.Pattern = "^\((\d\d\d)\)[ ]*(\d\d\d)[ .-](\d\d\d\d)[, .]*(x|ext|ext.)[ ]*(\d+)$" re.IgnoreCase = True Set mat = re.Execute(Phone) If mat.Count > 0 Then phAreacode = mat(0).SubMatches(0) phExchange = mat(0).SubMatches(1) phNumber = mat(0).SubMatches(2) phExtension = mat(0).SubMatches(4) End If '123.234.2344x1234 re.Pattern = "^(\d\d\d)[ .-](\d\d\d)[ .-](\d\d\d\d)[, .]*(x|ext|ext.)[ ]*(\d+)$" re.IgnoreCase = True Set mat = re.Execute(Phone) If mat.Count > 0 Then phAreacode = mat(0).SubMatches(0) phExchange = mat(0).SubMatches(1) phNumber = mat(0).SubMatches(2) phExtension = mat(0).SubMatches(4) End If If (phExtension <> "") Then NormalTel = phAreacode & "-" & phExchange & "-" & phNumber & " x" & phExtension Else NormalTel = phAreacode & "-" & phExchange & "-" & phNumber End If ' No number was detected, lose the dashes. Copy input if the it didn't get detected. If NormalTel = "--" Then If (Phone <> "") Then NormalTel = Phone Else NormalTel = "" End If End If End Function ~~~~

Reply

The content of this field is kept private and will not be shown publicly.
  • Lines and paragraphs break automatically.

More information about formatting options

12 + 1 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.