How to Clean & Format Phone Numbers – Excel & Google Sheets

This tutorial will demonstrate how to clean and format phone numbers in Excel and Google Sheets.

Sometimes, when importing phone numbers from different sources, the phone numbers come in different formats.  In other to have a presentable and uniform data, the phone numbers may need to be cleaned and formatted.

First, we will discuss how to clean unwanted characters from the phone number.

How to Clean Phone Numbers

Phone numbers can come in many different formats, with many different non-numeric characters. The SUBSTITUTE Function can be used to remove non-numeric characters like this:

=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(B3,"(",""),")","")," ",""),"-",""),".",""),"+",""))+0

The SUBSTITUTE Function replaces a specified character with another character. In the example above, the characters identified in the phone number column are replaced with blanks.

Note: The “+0” at the end of the formula converts the cleaned phone numbers to number format. Instead you could perform any other math operation or use the VALUE Function.

We’ve nested several SUBSTITUTE Functions in one formula, in order to remove all unwanted characters at once.

Instead you could split out each Function into separate cells like this:

Formatting Phone Numbers using the TEXT Function

After the phone numbers have been cleaned, we will need to format it. One way we can format the rows of phone numbers is by using the TEXT Function.

=TEXT(C3,"(###) ### ####")

The TEXT Function formats a cell based on a specified format. In the example above the format specified shows that the first 3 digits (area code) should be in parenthesis, while the remaining 7 digits are to be separated by a hyphen after the third digit.

Note: The character “#” within the TEXT formula is used to represent a digit, the number of times it is typed is used to determine the total number of digits to be displayed.

The TEXT Function converts the phone numbers into text. This may or may not be what you want. Below we will discuss a way to keep the phone numbers stored as numbers, while updating the formatting.

Formatting Phone Numbers Using Custom Number Formatting

Another way to format phone numbers is by using the custom number formatting in Excel. Using custom formatting gives us the opportunity to specify the format we want for a selected range.

Note: Custom Number Formatting only changes how the numbers are displayed. It does not change the actual contents of the cell.

First off, we select the cells to be formatted and then press Ctrl + 1. This brings up the format cells dialog box we see above.

Select Custom and then you can see the example options available to you or you can enter your own formatting.

In our example, we used the format “+1 ###-###-####”. The “+1” here represents the country code and this will be added to the phone number.

 

This method is also a great option to find your desired formatting before using the TEXT Function method.

Clean Format Phone numbers in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.