# How to Format a Phone Number with Extension in Excel – 3 Methods

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.

### 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

## Related Articles

<< Go Back to Phone Number Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF