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