Format Phone Numbers With Excel VBA

Objective:

We want to write a VBA code that can take phone numbers inputted in various formats and convert them into a uniform format, such as (XXX) XXX-XXXX (common format used in the United States).

Example Input Formats:

• 1234567890
• 123-456-7890
• 123.456.7890
• (123) 456-7890
• +1 123 456 7890

Desired Output Format:

• (XXX) XXX-XXXX

Steps and Approach:

1. Understanding the Phone Number Formats: Phone numbers can come in various formats. Our goal is to strip out any non-numeric characters, ensuring that the output always has 10 digits in the format (XXX) XXX-XXXX.
2. VBA Code Overview:
   • We will use a function that checks each phone number cell in the range, removes all non-numeric characters, then formats the phone number according to the desired format.
   • The function will validate that the phone number has exactly 10 digits before applying the format.

Detailed VBA Code:

Sub FormatPhoneNumbers()
    Dim cell As Range
    Dim phoneNumber As String
    Dim formattedNumber As String
     ' Loop through each cell in the selected range
    For Each cell In Selection
        If Not IsEmpty(cell.Value) Then
            phoneNumber = cell.Value
            ' Remove any non-numeric characters
            phoneNumber = CleanPhoneNumber(phoneNumber)           
            ' Check if the phone number has exactly 10 digits
            If Len(phoneNumber) = 10 Then
                ' Format the phone number as (XXX) XXX-XXXX
                formattedNumber = "(" & Mid(phoneNumber, 1, 3) & ") " & Mid(phoneNumber, 4, 3) & "-" & Mid(phoneNumber, 7, 4)
                cell.Value = formattedNumber
            Else
                ' If the phone number is not 10 digits, show an error message
                MsgBox "The phone number in cell " & cell.Address & " is not valid. It must have 10 digits.", vbExclamation
            End If
        End If
    Next cell
End Sub

' Helper function to clean the phone number by removing non-numeric characters
Function CleanPhoneNumber(ByVal phoneNumber As String) As String
    Dim i As Integer
    Dim cleanNumber As String
    cleanNumber = ""  
    ' Loop through each character in the string
    For i = 1 To Len(phoneNumber)
        ' If the character is a numeric digit, add it to the cleanNumber string
        If Mid(phoneNumber, i, 1) Like "#" Then
            cleanNumber = cleanNumber & Mid(phoneNumber, i, 1)
        End If
    Next i   
    ' Return the cleaned phone number
    CleanPhoneNumber = cleanNumber
End Function

Explanation of the Code:

1. Sub FormatPhoneNumbers:
   • This is the main subroutine that processes the selected range of cells (where the phone numbers are located).
   • The code loops through each cell in the selected range and checks if the cell is not empty (If Not IsEmpty(cell.Value)).
   • For each non-empty cell, it calls the helper function CleanPhoneNumber to remove any non-numeric characters.
2. CleanPhoneNumber Function:
   • This helper function takes the input phone number as a string and removes all non-numeric characters (e.g., hyphens, spaces, parentheses).
   • It loops through each character of the phone number and checks if the character is numeric (If Mid(phoneNumber, i, 1) Like « # » Then).
   • If the character is numeric, it appends it to the cleanNumber string.
   • Finally, it returns the cleaned-up phone number, which consists of only numeric characters.
3. Formatting the Phone Number:
   • Once the number has been cleaned (i.e., non-numeric characters removed), the code checks if the number has exactly 10 digits.
   • If it has 10 digits, it formats the number as (XXX) XXX-XXXX using the Mid function. The Mid function extracts parts of the string, e.g., Mid(phoneNumber, 1, 3) gets the first three digits.
   • If the number doesn’t have 10 digits, it displays a message box alerting the user that the phone number is invalid.
4. User Interaction:
   • The user selects the range of cells that they want to format, then runs the macro.
   • The macro will loop through each phone number in the selected range, clean it, and apply the desired format.

How to Use:

1. Open your Excel workbook and press Alt + F11 to open the VBA editor.
2. In the editor, click on Insert > Module to create a new module.
3. Paste the code provided into the module.
4. Close the editor and go back to your Excel sheet.
5. Select the range of cells containing phone numbers.
6. Press Alt + F8, select FormatPhoneNumbers, and click Run.

The phone numbers will be formatted as (XXX) XXX-XXXX, with non-numeric characters removed.

Example:

1. Input: 1234567890
   • Output: (123) 456-7890
2. Input: 123-456-7890
   • Output: (123) 456-7890
3. Input: +1 123 456 7890
   • Output: (123) 456-7890
4. Input: (123) 456-7890
   • Output: (123) 456-7890

Notes:

• The code assumes that valid phone numbers contain exactly 10 digits. If the number doesn’t meet this requirement, an error message is shown.
• You can modify the format in the code if you’d like a different phone number format.
• This code is for U.S.-style phone numbers, but you can adjust it for different regions if needed (e.g., handling country codes).