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.

Sample Dataset to Format Phone Number with Extension in Excel


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.

Use of Custom Format Feature to Format Phone Number with Extension in Excel

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.

Use of Custom Format Feature to Format Phone Number with Extension in Excel

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.
="("&LEFT(C5,3)&")"&MID(C5,4,3)&"-"&MID(C5,7,4)&" ext"&MID(C5,11,99)

Format Phone Number with Extension by Using Excel Combined Functions 

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

Formula Breakdown

  • “(“&LEFT(C5,3)&”)—-> The LEFT function will return 3 characters from the left-side.
    • Output: 101
  • “(“&LEFT(C5,3)&”)—-> Here, the Ampersand (&) will add Paranthesis.
    • Output: “(101)”
  • MID(C5,4,3)—-> The MID function will return 3 characters from the middle of the selected number starting from the 4th character.
    • Output: “345”
  • MID(C5,7,4)—-> It becomes
    • Output: “6789”
  • MID(C5,11,99)—-> It turns into
    • Output: “6”
  • ” ext”&MID(C5,11,99)—->
  • ” ext”&6—-> Here, the Ampersand (&) concatenates the text ext with the number 6.
    • Output: ” ext6″
  • “(“&LEFT(C5,3)&”)”&MID(C5,4,3)&”-“&MID(C5,7,4)&” ext”&MID(C5,11,99)
  • “(“&101)&”)”&345&”-“&6789)&” ext”&6
    • Output: (101)345-6789 ext6
    • Explanation: Here, by using the Ampersand (&) concatenate the different formats of the numbers with Extension.

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

Format Phone Number with Extension by Using Excel Combined Functions 

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.

Using VBA to Format Phone Number with Extension

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.

Using VBA to Format Phone Number with Extension

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

Code Breakdown

  • Here, I used multiple IF statements to check the number of digits. I’ve written the format for 8,10, and 12 digits, You can change it based on your need.
  • Next, I used the Value to select the cell Value then used the VBA Left, and Mid functions to give the format of my choice.

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.

Using VBA to Format Phone Number with Extension

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

Here, is the format of 3 types of digits.

Using VBA to Format Phone Number with Extension

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

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo