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. 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). This page explains custom number formats and with many examples.
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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
