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 in-built 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 having Names & Phone Numbers. Now we will Format the Phone Numbers with Extension using this dataset.

Download Practice Workbook

3 Ways to Format Phone Number with Extension in Excel

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 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 Extension.

Read More: How to Write Phone Number in Excel (Every Possible Way)

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 Extension.
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: How to Format Phone Number with Country Code in Excel (5 Methods)

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) where it will check each new entry in the Worksheet whether it fulfills 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 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.

Read More: Excel Formula to Change Phone Number Format (5 Examples)

Practice Section

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

Conclusion

I tried to explain 3 easy and quick ways to format phone number with extension 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 Add Dashes to SSN in Excel (6 Methods)
• Format TextBox for Phone Number with Excel VBA (A Step-by-Step Guide)
• [Solved!]: Excel Phone Number Format Not Working (4 Solutions)
• How to Format Phone Number with Dashes in Excel (2 Ways)
• How to Remove Parentheses from Phone Numbers in Excel (5 Methods)