Excel Formula to Change Phone Number Format

Even though Excel is a great tool for storing various types of data, it cannot automatically recognize phone numbers and does not format them properly. As a result, users must manually change the existing format to make the phone numbers more readable and visually appealing.

In this Excel tutorial, you will learn how to use different Excel formulas to change the phone number format in Excel. Here, I will mainly work with two cases: inconsistently formatted phone numbers and uniformly formatted phone numbers, and describe multiple formulas to change their formats.

In the image below, I have illustrated how the phone numbers would look before and after changing their formatting.

Changing Inconsistently Formatted Phone Number to a Specific Format

In this section, I will work on a dataset that contains some phone numbers with no uniform formatting.

If you look closely, you will see that each phone number is formatted differently. For example, some have dashes between digits, while others have spaces. Our goal is to make all the phone numbers have the same format. You can achieve this by using any of the methods listed below:

Method 1: Combining SUBSTITUTE & TEXT Functions

In this method, I’ll be using the SUBSTITUTE function, and the TEXT function to change the phone number format. The SUBSTITUTE function is used to remove unnecessary characters (dashes, commas, blanks etc) from the phone numbers and make them plain numbers. On the other hand, the TEXT function formats the plain number to our desired formatting.

To change the phone number format using the combination of SUBSTITUTE and TEXT functions, follow the steps below:

Select a blank cell and type the following formula:
=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,")",""),"(","")," ",""),"-",""), "(###) ### ####")
Replace C5 with the cell containing the phone number whose format you need to change.
Click Enter.
Consequently, the format of the phone number will be changed to (###) ### #### format.
Here I have changed the formatting of the phone number by replacing all the dashes with spaces and enclosing the area code (first three digits) using parentheses. To highlight the phone number’s area code, the first three digits are enclosed in parentheses in the format code.
Now, use the Fill Handle to fill the rest of the cell with the same formula.

As a result, all the phone numbers will be formatted with common formatting.

In the formula, I used the SUBSTITUTE function four times to get rid of four different kinds of non-number symbols like commas, spaces, and parentheses. But if there are more non-number symbols, you’ll need to add more SUBSTITUTE functions to the formula to remove them. Also, if you want the numbers to look a certain way when displayed, you can change the formatting code “(###) ### ####” in the argument of the TEXT Function.

Method 2: Combining TEXTSPLIT, TEXTJOIN, and TEXT Functions

The previous method has a significant drawback since you have to use the SUBSTITUTE function repeatedly for each non-numeric value in your dataset. This can become quite cumbersome, leading to excessively long formulas. In this alternative method, I’ll utilize the TEXTSPLIT function to achieve the same outcome more efficiently. However, the TEXTSPLIT function is only available in Microsoft 365.

To know how to change the formatting of phone numbers using the combination of the TEXTSPLIT, TEXTJOIN, and TEXT functions, follow the steps below:

Select a blank cell and type the following formula:
=TEXT(TEXTJOIN("",1,TEXTSPLIT(C5,{"(",")","-"," ","."},,1))+0, "(###) ### ####")
Replace C5 with the cell reference containing the phone number.
Press Enter to get the result.
Now, use the Fill Handle to copy the formula to the rest of the cells.

As a result, the formatting of all the phone numbers will be changed to a common format.

In the formula, TEXTSPLIT function splits text using specified delimiters such as parentheses, hyphens, spaces, and dots. Here, the advantage is that you can package all the delimiters in a single array like this {“(“,”)”,”-“,” “,”.”} and use it in the argument. While splitting the text, it simultaneously removes the delimiters. The split texts are then joined back together using the TEXTJOIN function. Then 0 is added to convert it into a numeric value. Lastly, the resultant numeric values are formatted as phone numbers using the TEXT function.

Note: While converting the rejoined texts into a numeric value, it removes the leading zeros from phone numbers.

Changing Uniformly Formatted Phone Number to a Specific Format

In this section, I will show how to change the formatting of uniformly structured phone numbers to another specific format using various Excel functions. For illustration, I took a dataset that contains a list of phone numbers with the same formatting.

Our target is to transform these phone numbers into a new formatting style using Excel formulas. Here, I will show you 4 formulas with examples to change these phone numbers into some most common phone number formats.

Example 1: Changing to a Phone Number Format with First 3 Digits in Parentheses

In the first example, I will remove the first dash and enclose the first three digits of the phone number with parentheses. Typically, the first three digits of a phone number represent the area code, and separating the code with parentheses makes the phone number more readable.

To achieve this, I will use the combination of the LEFT, MID, RIGHT, and SUBSTITUTE functions to change the phone number format in Excel. To know more, follow the steps below:

Select a blank cell and type the following formula:
="("&LEFT(SUBSTITUTE(C5,"-",""),3)&")"&MID(SUBSTITUTE(C5,"-",""),4,3)&"-"&RIGHT(SUBSTITUTE(C5,"-",""),4)
Here, replace C5 with your phone number cell.
Now press Enter to get the result.
Now, use the Fill Handle to fill the rest of the cells.

As a result, the formatting of all the phone numbers will be changed accordingly.

In the formula, the SUBSTITUTE function removes any hyphens from the original number. The LEFT function extracts the first three digits, which are then enclosed within parentheses. The MID function extracts the next three digits, representing the middle part of the phone number. Finally, the RIGHT function captures the last four digits.

Note: This formula is very specific as it is only applicable for 10 digit phone numbers with hyphens in 4th and 8th place as delimiters.

Example 2: Replacing All Dashes from Phone Numbers

In this example, I will replace all the dashes from the dataset with spaces using the REPLACE and the TEXT functions. To do that, follow the steps below:

Select a blank cell and type the following formula:
=TEXT(REPLACE(REPLACE(C5,4,1,""),7,1,""),"### ### ####")
Here, replace C5 with your phone number.
Now press Enter to get the result.
Now, use the Fill Handle to fill the rest of the cells.

As a result, the formatting of all the phone numbers will be changed accordingly.

In the formula, the innermost REPLACE function eliminates the dash at the 4th position in the original number, and the result is fed into the outer REPLACE function. This outer function then removes the dash at the 7th position in the modified string. The resultant text, without any dashes, is formatted using the TEXT function with the pattern “### ### ####”.

Note: If you have dashes occurring more than twice, excessive use of the REPLACE function becomes cumbersome. In such instances, you can opt for the following formula, which utilizes the SUBSTITUTE function to efficiently eliminate all dashes:

=TEXT(SUBSTITUTE(C5,"-",""),"### ### ####")

Example 3: Changing Phone Number Format by Adding Country Code

Here, we’re going to use the Ampersand (&) operator to add the country code to phone numbers in Excel.

To change the formatting of the phone numbers by adding country code by using the ampersand operator, follow the steps below:

Type the following formula in a blank cell:
="+1 "&C5
Replace C5 with the phone number cell and +1 with the country code of your phone numbers.
Now, press ENTER.
Thus, we’ll get our first formatted phone number.
Finally, autofill the formula using Fill Handle.

Consequently, the country code will be added to the initial phone numbers to change the phone number format.

Example 4: Creating a Custom Format for All Phone Numbers

If your phone numbers are in a straightforward numeric format, you can effortlessly use the TEXT function to customize the formatting according to your preference.

To use the TEXT function to change the formatting of phone numbers to a very specific format, follow the steps below:

Select a blank cell and type the following formula:
=TEXT(C5, "(###) ### ####")
Here, replace C5 with the phone number cell and “(###) ### ####” with your desired number format code.
Now press Enter to get the result.
Use the Fill Handle to autofill the rest of the cells.

Thus, we’ve completed our task of creating an Excel formula to change the phone number format.

In the format code, it is assumed that the first three digits are area codes and hence they are enclosed inside parentheses and there will be spaces at 4th and 8th place.

Note: This formula will not work for a mixed format of initial phone numbers.

How to Ensure Uniform Phone Number Entry Using Data Validation

If you want to make sure that phone numbers are entered correctly in Excel, you can use Data Validation tool. Data Validation enables us to make sure that the users always enter a certain number of digits in a designated range.

To learn how to ensure uniform phone number entry using the Data Validation, follow the steps below:

First, select the range of cells where you want to apply the data validation.
Now, go to the Data tab > Data Tools group > Data Validation.
As a result, the Data Validation dialog box will open.
In the Data Validation dialog box:
- Go to the Settings tab.
- From the Allow dropdown, choose Custom.
- In the Formula field, type the following formula:
  =AND(ISNUMBER(C5),LEN(C5)=10)
  Replace C5 with the 1st cell of your selected range, and 10 with the number of digits your phone number will contain.
- Click OK.

As a result, the data validation has been set up so that you will receive an error message if you attempt to enter anything other than a 10-digit number.

This will prevent users from entering phone numbers in unwanted formats.

Download Practice Workbook

Download the practice workbook to practice along for free!

Change Phone Number Format.xlsx

Conclusion

In this article, I have shown how to use different Excel formulas to change phone number formats into some popular phone number formats such as phone numbers with country codes, dashes, parentheses, etc. I hope the provided formulas will be useful for you. If you have any suggestions or feedback, feel free to share them in the comment section.

Frequently Asked Questions

How Do I Add a Dash Between Phone Numbers in Excel?

To add dashes between phone numbers in Excel, use the following formula:
=TEXT(A1,”000-000-0000″)
Replace A1 with the cell containing the phone number in plain number format and customize the format code “000-000-0000” to match the specific number of digits in the phone number and adjust the dash positions accordingly.

Are There Built-In Phone Number Formats For Phone Numbers In Excel?

Yes, there are some built-in phone number formats in Excel which you can access from the Format Cells dialog box. To format your phone numbers with Excel built-in phone number formats, follow the steps below:

Select the cells that contain phone numbers
Press Ctrl+1 to open the Format Cells dialog box.
In the Format Cells dialog box:
- Go to the Number tab;
- Under Category, click on Special;
- Under the Locale(location), select your location from the available drop-down options;
  As a result, the available format types for that location will be displayed under the Type menu;
- Under the Type, select your desired format;
  You can preview the different types under “Sample” to help you choose.
- Then press OK.

As a result, your phone numbers will be formatted according to the chosen built-in format.

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

Get FREE Advanced Excel Exercises with Solutions!