Clean and reformat telephone numbers

An excel formula to clean and reformat telephone numbers

Related Functions

Sponsored Link

 Clean and reformat telephone numbers
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""),".","")+0

One way to clean up and reformat telephone numbers is to strip out all extraneous characters, then apply Excel's built-in telephone number format.

This formula above uses a series of nested SUBSTITUTE functions to strip out spaces, hyphens, periods, parentheses, and commas. 

You'll need to adjust the actual replacements to suit your data.

How this formula works

The formula runs from the inside out, with each SUBSTITUTE removing one character.

The inner most SUBSTITUTE removes the left parentheses, and the result is handed to the next SUBSTITUTE, which removes the right parentheses, and so on.

Whenever you use the SUBSTITUTE function, the result will be text. Because you can't apply a number format to text, we need to convert the text to a number. One way to do that is to add zero (+0), which automatically converts numbers in text format to numbers in numeric format.

Finally, the "Special" telephone number format is applied (column D).

White space trick for better readability

When nesting multiple functions, it can be difficult to read the formula and keep all parentheses balanced. Excel doesn't care about extra white space in a formula, so you can add line breaks in the formula to make the formula more readable. For example, the formula above can be written as follows:

 
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "(",""), ")",""), "-",""), " ",""), ".","")

Note that the cell appears in the middle, with function names above and substitutions below. Not only does this make the formula easier to read, it also makes it easier to add and remove substitutions. 

You can use this same trick to make nested IF statements easier to read as well.

Sponsored Link

0 votes. 0 / 5