How to Format Phone Number with Extension in Excel (3 Easy Ways)

You may need to format your phone numbers with an extension. You can easily do it by using Excel’s built-in features and formulas. In this article, we will learn about Excel phone number format with extension using easy formulas & features.

Here, we have a dataset with Names & Phone Numbers. Now, we will format the phone numbers with extensions using this dataset.

How to Format Phone Number with Extension in Excel: 3 Easy Ways

1. Use of Custom Format Feature to Format Phone Number with Extension

By using the Custom format Feature you can format the phone number with an extension.
Here, I’ve some phone numbers and will show you how to format these numbers.

To do so,

• Firstly, select the cell or cell range to use Custom Format.
• Here, I selected the range C5:C10.
• Now, right-click on the mouse to open the Context Menu.
• Later, select Format Cells.

A dialog box of Format Cells will appear.

• From there, select Custom >> then in Type, insert the format you want to apply to your numbers.
• Here, I used (###) ###-### “ext” ##
• Finally, click OK.

As a result, you will get the phone numbers with an extension.

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

2. Format Phone Number with Extension by Using Excel Combined Functions

If you want, you can use the LEFT and MID functions to format phone numbers with extensions.
Let me explain the procedure to you,

• To begin with, select the cell of your choice to place the resultant value.
• Here, I selected cell D5.
• Now, type the following formula in the D5 cell or into the Formula Bar.

Here, I used the combination of LEFT and MID functions.

Now, press ENTER to get the expected phone number format with extension.

• Here, you can use the Fill Handle to AutoFill formula for the rest of the cells.

Read More: Excel Formula to Change Phone Number Format

3. Using VBA to Format Phone Number with Extension

You also can use the Visual Basic Application (VBA) to format phone numbers with extension. Here, I’m going to use a Private Sub, which will be applied on Sheet.

Let me explain the procedure to you,
First, open the Developer tab >> then select Visual Basic.

Then, it will open a new window of Microsoft Visual Basic for Applications.

• Now, select Sheet as it’s a Private Sub.

• Next, select Worksheet from General.

• Then, select Change from Declarations.

Now, write the following code in the Sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column <> 1 Then Exit Sub
    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)
    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)
    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, 3)
    Application.EnableEvents = True
    Exit Sub
    End With
    End If

End Sub

Here, I used a Private Sub Worksheet_Change(ByVal Target As Range), which will check each new entry in the Worksheet to see whether it fulfils the condition of the Target(Len) of the number if it meets the condition, the Phone Number will get format with an Extension.

Note: The code will work for column A.

• Now, Save the code and go back to your worksheet.
• Here, I typed 8 8-digit number in the A1 cell.

Then, press ENTER to get the format of your choice with Extension.

Here is the format of 3 types of digits.

Practice Section

Here, I have provided a practice sheet for you to practice the explained method.

Download Practice Workbook

Conclusion

I tried to explain 3 easy and quick ways to format phone number with extensions in Excel. These different ways will help you to format all types of numbers. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

Related Articles

• How to Use Phone Number Format in Excel
• 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