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

In Excel, storing phone numbers can be a bit tricky. Excel doesn’t always recognize them automatically, especially when there are extensions involved. This means you might need to manually format them to display them properly.

In this Excel tutorial, you will learn how to format phone numbers with extensions in Excel. Here, I will demonstrate formatting phone numbers with extensions using custom number format code, Excel functions, and VBA code.

In the image below, I have displayed phone numbers with extensions before and after formatting to illustrate how proper formatting can enhance the display of phone numbers with extensions.

What Are Phone Number Extensions?

An extension in a phone number is like a special code that helps you reach a specific person or department in a big organization. It’s like having a direct line to someone in a crowded room. When you dial a phone number with an extension, you’re telling the phone system where you want your call to go. The main part of the number gets you to the general area, and the extension pinpoints the exact spot.

For example, if you call a company and hear something like “Press 1 for sales, press 2 for support,” those numbers are extensions. They’re like secret passages that guide your call to the right destination, making sure you talk to the right person or department without getting lost in the phone line maze.

3 Ways to Format Phone Numbers with Extensions in Excel

In this section, I will discuss three effective ways to format phone numbers with extensions in Excel. Firstly, I will apply a custom number format that changes the appearance without modifying the original numbers. Secondly, we will use a special formula to adjust the original phone numbers and store them in separate locations. Lastly, I will provide a VBA code that completely transforms the original phone numbers, storing the modified data in the same locations as the originals. Each method has its own set of advantages and disadvantages. Let’s explore them one by one.

Using Custom Number Format

Excel provides many built-in number formats that allow users to change the appearance of numbers without changing the actual numbers. However, Excel does not have any built-in number format to display phone numbers with extensions. Hence, I will use a custom number format. To demonstrate this, I have taken a dataset that contains some names and phone numbers.

Each phone number has 12 digits, and the last 2 digits represent the extensions. Our goal is to format the phone numbers in such a way that the extensions are easily distinguishable.

To use custom number format to format phone numbers with extensions, follow the steps below:

Select the cell range that contains phone numbers with extensions.
Now, right-click on the mouse to open the context menu.
From the context menu, click on Format Cells.As a result, the Format Cells dialog box will open.
Note: You can also open the Format Cells dialog box by pressing Ctrl+1 shortcut keys.
In the Format Cells dialog box:
- Go to the Number tab > Custom category.
- In the Type field, write the following number format code:
  (###) ###-#### "ext" ##
  Here, each # represents a numerical value. I added “ext” before the last two digits to indicate they are extensions.
- Now, click OK.

As a result, the phone numbers will be displayed with the extensions clearly identifiable.

Remember that this method only changes the appearance of phone numbers, not the numbers themselves. To confirm, if you select a cell and look in the formula bar, you will only see the original phone number without formatting.
Note: The custom number format code is only applicable for a 12-digit phone number with the last two digits being extensions. If your phone numbers vary in length, you need to adjust the format code accordingly.

Using Excel Functions

In the previous method, it was assumed that all phone numbers would have the same length. However, in reality, phone numbers can have varying lengths. If you encounter this situation, you can use an advanced formula that includes Excel functions LEN, LEFT, and MID to handle it.

To illustrate, I have taken another dataset containing phone numbers of different lengths.

Each phone number has a main part consisting of the first 10 digits and an extension for the rest of the digits.

To know how to combine the LEN, LEFT and MID functions to format those phone numbers with extensions, follow the steps below:

Select the cell of your choice to place the resultant value.
Type the following formula in the cell:
="("&LEFT(C5,3)&") "&MID(C5,4,3)&"-"&MID(C5,7,4)&" ext "&MID(C5,11,LEN(C5)-10)
Replace C5 with the cell reference where the phone number is stored.
Now, press Enter.
Now, drag the Fill Handle to copy the formula for the rest of the cells.

As a result, all the phone numbers will formatted properly where the extensions are clearly noticeable.

In the formula, the LEFT function captures the first three characters (area code), the MID function extracts the middle three and four characters (first and second parts of the number), and finally, the LEN function helps with the extension part. By combining these functions with “&” and adding some parentheses and dashes, the formula displays a neat phone number with an extension.

Note: This formula will return an error if the phone number has less than 10 digits. In such cases, you should adjust the arguments of the LEFT and MID functions accordingly.

Running VBA Code

In this method, I will provide a Visual Basic Application (VBA) code that will automatically format a phone number with an extension as soon as you input it into the cell. This will be an event-driven macro hence, it will run automatically whenever you type anything on a specific column (in my case, it is column C).

To know how to use VBA code to format phone numbers with extension, follow the steps below:

First, go to the Developer tab > Visual Basic.
As a result, the Microsoft Visual Basic for Applications window will open up.
Note: You can also use the Alt+ F11 shortcut key to open this window.
Now from the appeared VBA window, select the sheet where you want to run the code.
Next, select Worksheet from the left dropdown in the code window.
Then, select Change from the right dropdown in the code window.

Now, paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 3 Then Exit Sub 'run only when the active cell is on Column C
    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)
    'Format = XXX-XXX-extXX
    
    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)
    'Format = XXX-XXXX-extXXX
    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, 2)
    'Format = XXX-XXX-XXXX-extXX
    Application.EnableEvents = True
    Exit Sub
    End With
    End If
End Sub

Note: The code will work for column C. To change the column, substitute the 3 with your desired column number in the ” If Target.Column <> 3 Then Exit Sub ” line.

Now, Save the code by pressing Ctrl +S and go back to your worksheet.
Choose any cell from column C and enter a phone number with either 8, 10, or 12 digits. As a result, they will automatically formatted with extensions.

Note: In the VBA code provided, it is assumed that phone numbers with 8, 10, and 12 digits have extensions in the last 2, 3, and 2 digits respectively. If you need to format phone numbers with a different number of digits or have extensions in different positions, you can modify the code accordingly using similar formulas.
This method will be very handy for you if you need to store phone numbers in a particular column very frequently.

Download Practice Workbook

Phone Number Format with Extension.xlsm

Conclusion

In this article, I tried to explain 3 easy and quick ways to format phone numbers with extensions in Excel. These different ways will help you to format all types of phone numbers regardless of the number of digits they contain. Also, you will be able to automate formatting using the provided VBA macro. If you have any suggestions, ideas, or feedback, please feel free to comment below.

Frequently Asked Questions

How Do I Auto Format Phone Numbers in Excel?

To quickly format phone numbers in Excel using the Special category, follow these easy steps: first, select the cell or cells you want to format. Then, press Ctrl+1 to open the Format Cells dialog box. In the dialog box, go to the Number tab > Category section > Special > Type > Phone Number. Finally, click OK. Voila! Your phone numbers will now be formatted automatically.

How Do I Validate a 10 Digit Mobile Number in Excel?

To validate a 10 digit mobile number in Excel, follow the steps below:

Select the range of cells where you will store the mobile numbers.
Now, go to the Data tab > Data Tools group > Data Validation option.
As a result, the Data Validation dialog box will open.
In the Data Validation dialog box:
1. Go to the Settings tab.
2. From the Allow dropdown, choose Custom.
3. In the Formula field, write the formula:
  =AND(ISNUMBER(C3),LEN(C3)=10)
  Replace C3 with the first cell of the selected range and 10 with the number of digits the mobile number must contain.
4. Click OK

Consequently, whenever you try to input mobile numbers in the selected range, the cell will only accept values that are 10 digits long and composed only of numbers.

Why Is It Important to Format Phone Numbers with Extensions in Excel?

Formatting phone numbers with extensions in Excel enhances readability and clarity, making it easier to identify and manage contact information, especially in large datasets. It ensures that the extensions are visibly distinguishable, streamlining communication processes.

<< Go Back to Phone Number Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!