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.
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).
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.
Background A common annoyance with data is that it may be represented as text instead of numbers. This is especially common with dimensions, which may appear in one text string that includes units, for example: 50 ft x 200 ft 153 ft x 324 ft Etc. In...
The SUBSTITUTE function lets you replace text by matching content. In this case, we want to remove hyphens from telephone numbers. The SUBSTITUTE function can handle this easily — we just need to provide a cell reference (B6), the text to remove...
The SUBSTITUTE function can find and replace text in a cell, wherever it occurs. In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string (""), which effectively removes the character...
In this example, the goal is to remove non-numeric characters from a text string with a formula. Working from the inside out, the MID function is used to extract the text in B5, one character at a time. The key to this step is the use of the ROW...
At the core, the formula uses the SUBSTITUTE function to perform the each substitution, with this basic pattern: = SUBSTITUTE ( text , find , replace ) "Text" is the incoming value, "find" is the text to look for, and "replace" is the text to...
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.