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

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample Dataset to Format Phone Number with Extension in Excel


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.

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 an extension.

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

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.
="("&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.

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

Format Phone Number with Extension by Using Excel Combined Functions 

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.

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), 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.

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


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo