How to Clean Phone Numbers in Excel (Format, Strip, Standardize)

The CRM export problem

Sales reps enter phone numbers however they want. By the time you export the contact list, you have (415) 555-0100, 415.555.0100, +1 415-555-0100, and 4155550100 all in the same column. The bulk-SMS tool you're about to upload to wants plain digits with no formatting. Excel does not have a one-click "clean phone numbers" command.

Here are the three real ways to fix this. The right one depends on whether you do this once a year or every Monday.

TL;DR — Key takeaways

• A nested SUBSTITUTE formula can strip parens, dashes, dots, and spaces but leaves you with a helper column.
• Power Query handles cleanup as a refreshable step, perfect for recurring imports.
• A VBA macro is the right answer when you want plain text digits in place, with the option to keep a leading + for international numbers.
• Force the output to text format so leading zeros and long international numbers don't get mangled to scientific notation.

Why phone numbers need cleaning

Phone numbers aren't arithmetic. They have:

• Leading zeros (UK, Italy, many other countries' landlines start with 0).
• Country codes (a 13-digit +919876543210 for India).
• Inconsistent formatting between data sources.
• Embedded text like ext 22 or (mobile).

Most downstream systems (SMS gateways, dialers, CRM imports) want a single canonical format. Often that's "plain digits, optionally with a leading +". Sometimes it's a specific dashed format. Either way, you start by stripping the noise.

Method 1: Nested SUBSTITUTE formula

Excel's SUBSTITUTE() function replaces one character at a time. To strip all the typical phone-number formatting, you nest it.

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

That removes parens, dashes, spaces, and dots. To also strip the leading +:

=SUBSTITUTE(SUBSTITUTE(...))

(Add another level of nesting.)

Workflow:

1. Insert a helper column.
2. Drop the formula in the first row.
3. Fill down.
4. Copy, Paste Special > Values back over the original.
5. Delete the helper.
6. Format the original column as Text so leading zeros don't get stripped.

The catch: five levels of nested SUBSTITUTE is a lot to type and edit. If a new character shows up in the data (a slash, a quote, an "ext"), you have to add another level. Workable for a one-off, fragile for repeat use.

Method 2: Power Query column transform

Power Query has Replace Values steps that compose nicely. Each one strips a character.

Steps:

1. Load your data into Power Query.
2. Right-click the phone column > Replace Values, replace ( with empty.
3. Repeat for ), -, , and ..
4. (Optional) Replace + with empty if you don't want the international prefix.
5. Change the column type to Text.
6. Close & Load.

The query persists, so next week's CSV import goes through the same cleanup automatically.

The catch: for a one-off, building the query is more work than running a macro. For recurring, this is the right answer.

Method 3: The free VBA macro (one-click, in place, text-formatted)

Download Clean Phone Numbers. It's a free .xlsm with one macro.

Steps:

1. Select the column of phone numbers.
2. Press Alt + F8, pick CleanPhoneNumbers, click Run.
3. Choose whether to keep a leading + for international numbers (recommended) or strip everything to plain digits.
4. Done. Each cell is rewritten in place as text-formatted plain digits.

The macro forces text format on the output so 0044... doesn't lose its leading zeros and 4155550100 doesn't render as scientific notation.

Comparison: which method to use

Method	Setup time	Handles new characters	Persists between runs	Best for
Nested SUBSTITUTE	2 min	No (manual edit)	Helper column	One-time cleanup of clean data
Power Query	5 min	Yes (add a step)	Yes (query)	Weekly/monthly recurring imports
VBA macro	5 sec (after install)	Yes (handles all non-digits)	No (one-shot per run)	One-shot cleanup of any incoming data

What "+1 (415) 555-0100" should become

A typical North American mobile number arrives as one of:

• +1 (415) 555-0100
• 1-415-555-0100
• (415) 555-0100
• 415.555.0100
• 4155550100

After cleaning to "plain digits with optional +", they all become +14155550100 or 14155550100. Same string for the same number, ready for upload, dedupe, or lookup.

Common scenario: cleaning a weekly CRM export

Every Monday you import a CRM contact list to feed the SMS reminder tool. The macro version of the workflow:

1. Open the CSV in Excel.
2. Select the phone column.
3. Run Clean Phone Numbers, choose Yes to keep +.
4. Save the file as CSV.
5. Upload.

Elapsed: about 30 seconds, all 1,200 numbers cleaned, leading zeros preserved, no scientific-notation surprises.

Frequently asked questions

Why does the macro write text instead of numbers?

Phone numbers aren't numeric. Treating them as numbers means Excel will silently turn 4155550100 into 4.15555E+09 and strip leading zeros from international numbers. The macro forces text format on each modified cell to prevent this. Your downstream system (SMS tool, CRM, database loader) almost certainly wants text anyway.

What about extensions like "ext 22"?

The macro strips them along with everything else, so 415-555-0100 ext 22 becomes 415555010022 (the digits from "22" get appended). If extensions matter for your data, separate them into their own column with Text to Columns first, then run the macro on the main number column.

Can it format the result like (415) 555-0100?

No, the macro normalizes to plain digits. To re-format afterward, use a separate formula like =TEXT(VALUE(A2), "(000) 000-0000") or write a tiny "FormatPhoneNumbers" companion macro. Most users don't need formatted output; they need clean digits to feed into another system.

Does it validate that the result is a real phone number?

No. Validation rules vary wildly by country (10 digits in the US, 11 to 13 with country code in India, etc.) and a wrong validation rule is worse than none. The macro normalizes formatting; if you need validation, add it as a separate step or a country-specific lookup.

Will it merge two numbers in the same cell?

Yes. If a cell contains 415-555-0100 / 503-555-0200, both numbers' digits get concatenated as 41555501005035550200. Most CRMs put one phone per cell, so this rarely bites. If your data has multiple numbers per cell, split them with Excel's Text to Columns first, then run the macro on each resulting column.

What to do next

If your phone column came from a CSV with other dirty data, run Trim Whitespace and Convert Text to Numbers on the rest of the columns too. If your data has email addresses mixed in with the phone numbers in free-form notes, see Extract Email Addresses From Text. Either way, the Clean Phone Numbers macro is the fastest single-step fix for the phone column itself.