Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Get column index in Excel Table
  • Conditional Formatting Color Scales Examples in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How To Sort One Column or Multiple Columns in Excel

References

  • How to retrieve first match between two ranges in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • How to get address of named range in Excel
  • Vlookup Examples in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation must begin with
  • Excel Data validation must contain specific text
  • Data validation must not exist in list
  • Excel Data validation exists in list

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.

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

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:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

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

Logical Functions

  • OR function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • Invoice status with nested if in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get work hours between dates and times in Excel
  • How to calculate project start date based on end date in Excel
  • Two ways to sum time over 30 minutes in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • Display Days in month in Excel

Grouping

  • Running count group by n size in Excel
  • Group times into 3 hour buckets in Excel
  • Group numbers at uneven intervals in Excel
  • Map inputs to arbitrary values in Excel
  • Calculate conditional mode with criteria in Excel

General

  • Delete Blank Rows at Once in Excel
  • Count cells less than in Excel
  • How to make excel worksheets print on one page?
  • Creating and Opening an existing file in Excel
  • How to fill cell ranges with random text values in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning