Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get address of first cell in range in Excel
  • How to use Excel LOOKUP Function
  • Extract all partial matches in Excel
  • How to create dynamic named range with OFFSET in Excel
  • How to use Excel MMULT Function

Data Analysis Examples

  • How To Insert and Customize Sparklines in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How to create Checklist in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS

Data Validation Examples

  • Excel Data validation whole percentage only
  • Excel Data validation allow uppercase only
  • Excel Data validation specific characters only
  • Excel Data validation date in next 30 days
  • Excel Data validation only dates between

Clean and reformat telephone numbers using SUBSTITUTE function in Excel

by

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.

Formula

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

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

Explanation

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

Worked Example:   Change Fractions Format in Excel

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.

Worked Example:   Quickly Highlight Cells Containing Different Content in a Row

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).

Worked Example:   Merge Cells in Excel

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:

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

Post navigation

Previous Post:

How to count keywords in a range of cell

Next Post:

How to count line breaks in cell in Excel worksheet

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • TEXTJOIN function: Description, Usage, Syntax, Examples and Explanation
  • CHAR function: Description, Usage, Syntax, Examples and Explanation
  • Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel
  • DOLLAR function: Description, Usage, Syntax, Examples and Explanation
  • CONCAT function: Description, Usage, Syntax, Examples and Explanation
  • Get age from birthday in Excel
  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • Add workdays to date custom weekends in Excel
  • Excel Date & Time Functions Example
  • How to calculate workdays per month in Excel
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
  • Calculate original loan amount in Excel
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan interest in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles