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

The dataset contains names and phone numbers. Use 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.

Read More: How to Format Phone Number with Dashes in Excel

Method 2 – Using Excel Functions

Use the LEN, LEFT, and MID 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.

Read More: Excel Formula to Change Phone Number Format

Method 3 – Running a VBA Code

Use the Visual Basic Application (VBA).

• 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

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

Related Articles

• How to Write Phone Number in Excel
• Keep 0 Before a Phone Number in Excel
• How to Format Phone Number with Country Code in Excel
• How to Remove Parentheses from Phone Numbers in Excel
• [Solved!]: Excel Phone Number Format Not Working

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