Excel Formula to Change Phone Number Format (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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


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.

Excel Formula to Change Phone Number Format

  • 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.

Excel Formula to Change 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.

Steps:

  • 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.

Excel Formula to Change Phone Number Format

  • 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.

Excel Formula to Change Phone Number Format

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.

Excel Formula to Change Phone Number Format

  • 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.

Excel Formula to Change 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.

Excel Formula to Change Phone Number 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.

Excel Formula to Change Phone Number Format

Steps:

  • Firstly, type the following formula in cell D5.
="+1 "&C5

We’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.

Excel Formula to Change Phone Number Format

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.

Excel Formula to Change Phone Number Format


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!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo