Rafiul Haq
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.
Download Practice Workbook
5 Ways to Use Formula to Change Phone Number Format in Excel
1. Using the TEXT Function to Change Phone Number Format
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.
Read More: How to Write Phone Number in Excel (Every Possible Way)
2. Using Formula of SUBSTITUTE & TEXT Functions to Change Phone Number Format
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’ve 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 (4 Solutions)
3. Combining Functions to Create an Excel Formula to Change Phone Number Format
We’re going to use the LEFT function, the MID function, the RIGHT function, and the SUBSTITUTE function 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 Format Phone Number with Extension in Excel (3 Easy Ways)
4. Merging the REPLACE and the TEXT Functions to Change Phone Number Format
For method 4, we’re going to use the REPLACE function and the TEXT function 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 Add Dashes to SSN in Excel (6 Methods)
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 our phone number.
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.
Read More: How to Format Phone Number with Country Code in Excel (5 Methods)
Practice Sheet
We’ve added practice datasets in the Excel file. So that, you can try out our methods.
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!
