How to Format a Phone Number with Extension in Excel

The image below showcases phone numbers with extensions before and after formatting:

Method 1 – Using a Custom Number Format

Each phone number has 12 digits. The last 2 digits represent the extensions.

Select the cell range containing phone numbers with extensions.
Right-click to open the context menu.
Click Format Cells.
Note: You can also open the Format Cells dialog box by pressing Ctrl+1.

In the Format Cells dialog box:
- Go to the Number tab > Custom.
- In Type, enter the following number format code:
  (###) ###-#### "ext" ##
  Here, each # represents a numerical value. “ext” before the last two digits indicate the extensions.
- Click OK.

Phone numbers will be displayed with the extensions.

Method 2 – Using Excel Functions

The dataset contains phone numbers with different length.

Each phone number has a main part consisting of the first 10 digits. The rest of the digits are the extension.

Select a cell to see the result.
Enter the following formula in that cell:
="("&LEFT(C5,3)&") "&MID(C5,4,3)&"-"&MID(C5,7,4)&" ext "&MID(C5,11,LEN(C5)-10)
Replace C5 with the cell reference in which the phone number is stored.
Press Enter.
Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

In the formula, the LEFT function gets the first three characters (area code), the MID function extracts the middle three and four characters (first and second parts of the number), and the LEN function returns the extension. By combining the functions with “&” and adding parentheses and dashes, the formula displays the phone number with the extension.

Note: This formula will return an error if the phone number has less than 10 digits. You must adjust the arguments of the LEFT and MID functions.

Method 3 – Running a VBA Code

Go to the Developer tab > Visual Basic.

Note: You can also press Alt+ F11 to open this window.
In the VBA window, select the sheet to run the code.
Select Worksheet.
Select Change.

Enter the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 3 Then Exit Sub 'run only when the active cell is on Column C
    If Target.Count > 1 Then Exit Sub
    
    If Len(Target) = 8 Then
    With Target
    Application.EnableEvents = False
    .Value = "(" & Left(.Value, 3) & ") " & Mid(.Value, 4, 3) & "-" & "ext" & Right(.Value, 2)
    'Format = XXX-XXX-extXX
    
    Application.EnableEvents = True
    Exit Sub
    End With
    End If
    
    If Len(Target) = 10 Then
    With Target
    Application.EnableEvents = False
    .Value = "(" & Left(.Value, 3) & ") " & Mid(.Value, 4, 4) & "-" & "ext" & Right(.Value, 3)
    'Format = XXX-XXXX-extXXX
    Application.EnableEvents = True
    Exit Sub
    End With
    End If
    
    If Len(Target) = 12 Then
    With Target
    Application.EnableEvents = False
    .Value = "(" & Left(.Value, 3) & ") " & Mid(.Value, 4, 3) & "-" & Mid(.Value, 7, 4) & _
    "-" & "ext" & Right(.Value, 2)
    'Format = XXX-XXX-XXXX-extXX
    Application.EnableEvents = True
    Exit Sub
    End With
    End If
End Sub

Note: The code will work in column C. To change the column, replace 3 with another column number in ” If Target.Column <> 3 Then Exit Sub “.

Save the code by pressing Ctrl +S and go back to your worksheet.
Choose any cell in column C and enter a phone number with either 8, 10, or 12 digits.

They will automatically be formatted with extensions.

Note: In the VBA code provided, it is assumed that phone numbers with 8, 10, and 12 digits have extensions in the last 2 and 3 digits. To format phone numbers with a different number of digits or extensions in different positions, modify the code.

Download Practice Workbook

Phone Number Format with Extension.xlsm

Frequently Asked Questions

How Do I Auto Format Phone Numbers in Excel?

To quickly format phone numbers in Excel use the Special category:

Select the cells you want to format.
Press Ctrl+1 to open the Format Cells dialog box.
Go to the Number tab > Category > Special > Type > Phone Number.
Click OK.

How Do I Validate a 10 Digit Mobile Number in Excel?

Select the cells to store the mobile numbers.
Go to the Data tab > Data Tools > Data Validation.
In the Data Validation dialog box:
- Go to Settings.
- In Allow, choose Custom.
- In Formula, use the formula:
  =AND(ISNUMBER(C3),LEN(C3)=10)
- Click OK