In this article, we’re going to show you 5 methods of creating an Excel formula to change phone number format. To demonstrate our methods, we’ve chosen a dataset that has 2 columns: Name and Phone. We’ll add another column to generate the same phone number format.
Excel Formula to Change Phone Number Format: 5 Examples
1. Using the TEXT Function to Make a Specific Format for All Phone Numbers
For the first method, we’ll use the TEXT function to change the phone number format. Our initial data is in the same format.
Steps:
- Firstly, type the following formula in cell D5.
=TEXT(C5, "(###) ### ####")Let’s look a closer look at the TEXT function.
Here the hash (“#”) means a digit. the output format is (3 digits), then blank space and 3 more digits, and finally blank space and the remaining 4 digits.
Note: This formula will not work for a mixed format of initial phone numbers. For that, see method 2.
- Secondly, press ENTER.
We’ve changed the format of the first phone number.
- Finally, use the Fill Handle to AutoFill the formula.
Thus, we’ve completed our task of creating an Excel formula to change the phone number format.
2. Formula to Apply Same Format to Differently Formatted Phone Numbers
In this method, we’ll be using the SUBSTITUTE function, and the TEXT function to change the phone number format. Although, we can use the TEXT function to change the format, however, we need the SUBSTITUTE function for a mixed format of the initial data.
- Firstly, type the following formula in cell D5.
=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,")",""),"(","")," ",""),"-",""), "(###) ### ####")Formula Breakdown
We’ve two parts in our formula. Firstly, the TEXT function, which is covered in method 1, and secondly the nested SUBSTITUTE function part. Therefore, we’re going to explain only the second part of the function here.
- SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,”)”,””),”(“,””),” “,””),”-“,””) -> becomes
- SUBSTITUTE(“166-776-6911″,”-“,””)
- Output: “1667766911”.
- This function does exactly what its name says. The function will replace any brackets, dashes, and spaces with a blank value. For the last part, we can see that it will replace all the dashes from cell C5 with blank in cell D5.
After that, our TEXT function will format the phone numbers.
- Secondly, press ENTER.
We’ve cleaned the phone number format with the help of the SUBSTITUTE function. After that, we used the TEXT function to format it.
- Finally, AutoFill the formula to the rest of the cells.
In conclusion, we’ve created another Excel formula to change the phone number format. Moreover, this is what the final step should look like.
Read More: [Solved!]: Excel Phone Number Format Not Working
3. Excel Formula to Create a Phone Number Format with First 3 Digits in Parenthesis
We’re going to use the LEFT, MID, RIGHT, and SUBSTITUTE functions to change the phone number format in Excel.
Steps:
- Firstly, type the following formula in cell D5.
="("&LEFT(SUBSTITUTE(C5,"-",""),3)&")"&MID(SUBSTITUTE(C5,"-",""),4,3)&"-"&RIGHT(SUBSTITUTE(C5,"-",""),4)Formula Breakdown
- “(“&LEFT(SUBSTITUTE(C5,”-“,””),3)&”) ” -> becomes,
- “(“&LEFT(“1667766911″,3)&”) “
- Output: “(166) “.
- We’ve explained the SUBSTITUTE function in method 2. The LEFT function returns a specific amount of characters from a string. We’ve selected 3 in order to show three characters from the left side. Besides that, the ampersand sign (&) is used to join characters. We’re adding the brackets using this.
- MID(SUBSTITUTE(C5,”-“,””),4,3)
- MID(“1667766911”,4,3)
- Output: 776
- The MID function returns characters from a specified position of a string. We’re telling it to return the 3 characters from the fourth position of the string.
- RIGHT(SUBSTITUTE(C5,”-“,””),4)
- RIGHT(“1667766911”,4)
- Output: 6911.
- The RIGHT function will return characters from the right side of the string. We’re telling it to return the first 4 characters from the right side of the string.
Here, the SUBSTITUTE function is modifying our string.
- Secondly, press ENTER.
Thus, we’ve formatted the first phone number.
- Finally, AutoFill the formula to the other cells.
Consequently, we’ve demonstrated to you the third method to change the phone number format.
Read More: How to Remove Parentheses from Phone Numbers in Excel
4. Formula to Replace All Dashes from Phone Numbers
For method 4, we’re going to use the REPLACE and the TEXT functions to change the phone number format.
Steps:
- Firstly, select the cell range D5:D10.
- Secondly, type the following formula.
=TEXT(REPLACE(REPLACE(C5,4,1,""),7,1,""),"### ### ####")Formula Breakdown
Firstly, we’re using the REPLACE function to clean our phone numbers. Secondly, we’re using the TEXT function to format the phone number.
- REPLACE(C5,4,1,””)
- Output: “166776-6911”.
- We’re using the REPLACE function to remove the dashes with blank values. Here, we’re replacing the first dash in position 4 of the string with a blank value.
- REPLACE(“166776-6911″,7,1,””)
- Output: “1667766911”.
- We’re replacing the remaining dash on position 7 with a blank value.
After that, the TEXT function will format it with the “3 digits space 3 digits space 4 digits” format.
- Finally, press CTRL + ENTER.
After that, the formula will AutoFill. Thus, we’ve demonstrated to you the fourth method of formatting phone numbers.
Read More: How to Format Phone Number with Dashes in Excel
5. Excel Formula to Change Phone Number Format by Adding Country Code
For the last method, we’re going to use the ampersand to add the country code to phone numbers in Excel.
Steps:
- Firstly, type the following formula in cell D5.
="+1 "&C5We’re joining “+1 blank space” with our phone number.
- Secondly, press ENTER.
Thus, we’ll get our first formatted phone number.
- Finally, AutoFill the formula.
In conclusion, we’ve added the US country code to our initial phone numbers to change the phone number format.
Practice Sheet
We’ve added practice datasets in the Excel file so that you can try out our methods.
Download Practice Workbook
Conclusion
We’ve shown you 5 methods to create an Excel formula to change phone number format. Moreover, you can comment below if you have any problems regarding these. Thanks for reading. Keep excelling!
