[Solved!]: Excel Phone Number Format Not Working (4 Solutions)

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.


Download Practice Workbook

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


4 Solutions to ‘Excel Phone Number Format Not Working’ Issue

To demonstrate the methods, let’s use the following dataset that contains some random phone numbers.


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

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 (2 Ways)


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

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

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 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 (5 Examples)


3. Use SUBSTITUTE Function If Phone Number Format Does Not Work

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 (5 Methods)


4. Use Find and Replace Option to Fix Phone Number Format

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.


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 question in the comment section and please give me feedback.


Related Articles

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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