Remove phone separators, keep the plus

Turn (201) 555-0123 into 2015550123. A leading + or 0 stays, as text.

What you'll make

In Excel: (201) 555-0123 becomes 2015550123.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The phone number, with brackets, a space and a hyphen."(201) 555-0123"
2SUBSTITUTE( … "(" … ")" … )Takes out both brackets."201 555-0123"
3SUBSTITUTE( … " " … )Takes out every space."201555-0123"
4SUBSTITUTE( … "-" … )Takes out every hyphen."2015550123"
5SUBSTITUTE( … "." … )Takes out every period. A + sign stays."2015550123"

Each bracket wraps the one inside it, so Excel works from the inside out. Part 2 is two brackets, one for ( and one for ). The numbers here are made-up.

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

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own numbers. Paste phone numbers into the green column of the Template tab. The digits fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Only brackets, spaces, hyphens and periods come out. Look over any result that still has letters or an extension.

Microsoft's help: SUBSTITUTE function