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 = "^(ddd)[ .-](dddd)[.,]*$"
    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 = "^(ddd)[ .-]*(dddd)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 = "^((ddd))[ ]*(ddd)[ .-](dddd)[.,]*$"
    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 = "^(ddd)[.-](ddd)[ .-](dddd)[.,]*$"
    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 = "^((ddd))[ ]*(ddd)[ .-](dddd)[, .]*(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 = "^(ddd)[ .-](ddd)[ .-](dddd)[, .]*(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
~~~~