Excel Formula to Change Phone Number Format (5 Examples)

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


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. 

Excel Formula to Change Phone Number Format

  • Secondly, press ENTER.

We’ve changed the format of the first phone number.

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

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

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

Excel Formula to Change Phone Number Format

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.

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

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

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.

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.

Excel Formula to Change Phone Number Format


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

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo