Excel Phone Number Format Not Working (2 Possible Cases)

Get FREE Advanced Excel Exercises with Solutions!

There are some built-in formats for phone numbers in Excel, so if you want to try other formats using the built-in formats, then, of course, it won’t work. But no worries! There is hope. I’ll introduce some tricky ways to overcome those situations if the phone number format is not working in Excel with clear steps and screenshots.


Excel Phone Number Format Not Working: 2 Possible Cases

To demonstrate the methods, let’s use the following dataset that contains some random phone numbers. But it has no formatting. Now, we will see 4 cases, when you are unable to apply the built-in phone number format of Excel, or, the built-in feature is not what you want, so you need to customize the phone numbers.


Case 1: Excel Built-In Phone Number Format Is Not What You Want So Use a Custom Format

Have a look that there are no hyphens or brackets between the numbers. Let’s see what happens if we change the format to a custom phone number format.

Steps:

  • Click on the Format Cells shortcut icon, as shown in the image below.

Soon after, a dialog box will open up.

Use Custom Option from Number Group If Phone Number Format Is Not Working

  • Then click on Special from the Category section.
  • Later, select Phone Number from the Type: section.

Use Custom Option from Number Group If Phone Number Format Is Not Working

It’s the default phone number format in Excel.

But if you want to keep the format like this: XXX-XXX-XXXX, then that process won’t work.

Use Custom Option from Number Group If Phone Number Format Is Not Working

Now let me show you how we can do it.

  • Click on Custom from the Category section.
  • Then type ###-###-#### in the Type: box.
  • Finally, just press OK.

Use Custom Option from Number Group If Phone Number Format Is Not Working

Now you see, our desired format worked well.

Read More: How to Format Phone Number with Dashes in Excel


Case 2: Your Numbers Have Hyphens in Them and Excel Rejects to Format Them as Phone Numbers

Let’s see another common issue. Here, the phone numbers are with hyphens.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

Now, if we try the built-in Phone Number format, then see what it returns.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

No change!

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

The reason the hyphens of the numbers were not returned with formatting is that they were typed in General format. Only the number format can be converted to the default phone number format.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work


Solution 1: Apply a Formula with LEFT, MID and RIGHT Functions to Get Rid of Hyphens and Convert the Output to Number First

So, to get the numbers without hyphens, we’ll use the LEFT, MID, and RIGHT functions. For that, I added a new column.

Steps:

  • Write the formula in Cell C5
=LEFT(B6,3)&MID(B6,5,3)&RIGHT(B6,4)

Later, just press the Enter button and see that all the hyphens are removed.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

  • Finally, drag down the Fill Handle icon to copy the formula for the other cells.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

Then, you will get all the numbers without hyphens.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

Now, we’ll have to convert the values into numbers. For that, I have added a new column.

  • Copy the outputs and.
  • Then select the first cell of the new column.
  • Later, right-click your mouse and paste them as values.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

The values are stored as text here.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

  • Click on the error icon and select Convert to Number.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

Now see that the error message is gone and converted to numbers.

Now you can change the format to a special Phone Number format.

We are done.


🔻 Formula Breakdown:

➽ LEFT(B5,3)
The LEFT function will return the first three digits from the number of Cell B5
“985”

➽ MID(B5,5,3)
The MID function will keep three digits starting from the fifth position of the number so that it will return as
“675”

➽ RIGHT(B5,4)
The RIGHT function will return the last four digits from the number of Cell B5
“4654”

➽ LEFT(B5,3)&MID(B5,5,3)&RIGHT(B5,4)
Finally, the & operator will join the outputs together, and that will return as
“9856754654”

Read More: Excel Formula to Change Phone Number Format


Solution 2: Apply SUBSTITUTE Function to Get Rid of Hyphens

You can remove the hyphens using the SUBSTITUTE function, too. It’s fast and easy compared to the previous method.

Steps:

  • In Cell C5, type the following formula-
=SUBSTITUTE(B5,"-","")
  • Then press the Enter button.

Use SUBSTITUTE Function If Phone Number Format Does Not Work

  • Finally, use the Fill Handle tool to finish.

Use SUBSTITUTE Function If Phone Number Format Does Not Work

And we get all the numbers without hyphens.

Now, we’ll have to convert the values into numbers. For that, I have added a new column.

  • Copy the result.
  • Now, select the first cell of the new column.
  • Right-click your mouse and paste them as values.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

The values are in text format here.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

  • To convert them to number format, click on the error icon and select Convert to Number.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

Now see that the error message is gone and converted to numbers.

Now, you can change the format to a built-in Phone Number format.

Here is the final result!

Read More: How to Remove Parentheses from Phone Numbers in Excel


Solution 3: Use the Find and Replace Tool to Replace the Hyphens

Here, we’ll use the Find and Replace tool if the phone number formats of Excel don’t work. We can easily remove all the hyphens at a time from numbers using this tool.

Steps:

  • Select the cells that contain numbers.
  • Press Ctrl+H to open the Find and Replace dialog box.
  • Then type a hyphen (-) in the Find what: box and keep the Replace with: box empty.
  • Finally, just press Replace All.

Soon after, you will see that the tool has removed all the hyphens successfully.

Now, we’ll convert these into numbers. For that, execute the following steps.

  • Copy the outputs.
  • Then select the first cell of the new column.
  • Later, right-click your mouse and paste them as values.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

The values are stored as text here.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

  • Click on the error icon and select Convert to Number.

Combine LEFT, MID, and RIGHT Functions If Phone Number Format Does Not Work

Now see that the error message is gone and converted to numbers.

Now you can change the format to a special Phone Number format.

Thus, we have successfully fixed the issue.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Conclusion

I hope the procedures described above will be good enough to solve the problem if the phone number format is not working in Excel. Feel free to ask any questions in the comment section, and please give me feedback.


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.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo