Go Back

Clean and reformat telephone numbers

Telephone numbers come in many forms, normally in combination with other characters like hyphens, periods and parentheses.  While working with Excel, we are able to clean telephone numbers using the SUBSTITUTE function.  This step by step tutorial will assist all levels of Excel users in cleaning and formatting telephone numbers.  

Figure 1. Final result: Clean and reformat telephone numbers

Final formula:  =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"-",""),".",""),"(",""),")","")," ","")+0

Syntax of the SUBSTITUTE function

SUBSTITUTE function replaces a character or text in a text string

=SUBSTITUTE(text, old_text, new_text, [instance_num])

  • text  – the text string containing the character or text we want to replace or change
  • old_text  – any text that we want to replace with new_text
  • new_text  – the text we want to replace old_text with
  • instance_num  – Optional; Specifies which occurrence of old_text we want to replace; If omitted, every occurrence of old_text in text is replaced with new_text

Setting up Our Data

Our table has three columns: Telephone Number (column A), Clean Version (column B), Reformatted (column C).  Note that the three telephone numbers listed have the same numbers but with different characters: dash “-”, period “.”, parentheses “()” and spaces.  

In column B, we want to obtain the clean version of the telephone numbers, with only the numerical digits, without the other characters.  In column C, we want to format the numbers according to our preference.

Figure 2. Sample data to clean and reformat telephone numbers

Using the SUBSTITUTE function

Before we proceed to the final formula to clean telephone numbers, let us first learn how to use the SUBSTITUTE function.  

Given below is the syntax for the SUBSTITUTE function:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

For example, we want to substitute the dash “-” in cell B4 with an empty string.  We enter the formula in cell C4: =(SUBSTITUTE(B4,”“,“”))

Figure 3. Entering the formula using SUBSTITUTE

The result in cell C4 is 2221234567, where the dashes are eliminated and only the numerical digits remain.  

Clean telephone numbers

We want to obtain only the numerical digits of our telephone numbers and eliminate the other characters.  The key here is to determine the characters that we want to eliminate and replace with the empty string. Looking at the telephone numbers, we want to substitute the dash “-”, the period “.”, the open and close parentheses “(“ and “)” and the space with an empty string “”.  

We will be using one SUBSTITUTE function for every character that we want to replace with an empty string.  In order to clean our telephone numbers, we follow these steps:

Step 1.  Select cell C5

Step 2.  Enter the formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"-",""),".",""),"(",""),")","")," ","")+0

Step 3:  Press ENTER

Step 4:  Copy the formula in cell C5 to cell C6 by clicking the “+” icon at the bottom-right corner of cell C5 and dragging it down

Figure 4. Using SUBSTITUTE to clean telephone numbers

Our formula is a combination of five SUBSTITUTE functions.  The first SUBSTITUTE function becomes the text for the second SUBSTITUTE function, and so on.  The additional “+0” at the end of the formula converts the telephone numbers into number format.  This is especially helpful when we want to reformat our telephone numbers.

In cells C5 and C6, we have successfully cleaned the telephone numbers, leaving only the numerical digits 2221234567.  

Reformat telephone numbers

First, let us copy the formulas of cells C4:C6 to cells D4:D6.

Figure 5. Copying the formulas in column C to column D

In order to format the telephone numbers to our preference, let us follow these steps:

Step 1.  Select cells D4:D6

Step 2.  Press Ctrl + 1 to launch the Format Cells dialog box

Step 3:  In the Format Cells dialog box, click Custom

Step 4.  In the Type: box,enter the custom format: (###) ###-####

Step 5.  Click OK.  

Figure 6. Entering the custom format

The format can be customized according to our preference.  We can incorporate other characters, or change the color of the numbers.  

For this example, our preferred format is (###) ###-####.  As a result, we have now reformatted the telephone numbers in cells D4:D6.  

Figure 7. Output: Reformat telephone numbers

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have a large database divided by sectors and excel files, i would like to add telephone numbers and replace existing contacts
Solved by O. J. in 17 mins
I need to change number format of telephone numbers eg: +919889012345 change to 9889012345
Solved by F. L. in 21 mins
clean up numbers by creating logical formulas that will work and make sense in relationship to other columns.
Solved by Z. Y. in 27 mins

Leave a Comment