Clean Phone Numbers

What it does

You have a column of phone numbers in five different formats. (415) 555-0100. 415.555.0100. +1 415-555-0100. 4155550100. 415 555 0100 ext 22. The tool walks your selection, strips every character except digits (and optionally a leading +), and writes each cell back as plain text so Excel doesn't drop leading zeros or convert long numbers to scientific notation. After running, every cell is just digits, ready for deduplication, lookup, or upload to whatever system needed them clean.

Real-world example

Picture an accounts-receivable clerk at a regional building-materials supplier. Every Monday she imports a customer list from the CRM into Excel to send late-payment reminders. The CRM lets sales reps enter phone numbers however they want, so on any given week she gets entries like (503) 555-0142, 503.555.0142, +1-503-555-0142, and the occasional 503/555-0142 (mobile). Her bulk-SMS tool requires plain digits with no formatting, or it silently fails to send.

Her current workflow: Find/Replace (, Find/Replace ), Find/Replace -, Find/Replace , Find/Replace ., Find/Replace +, then scroll through the column to make sure no rogue characters slipped through. She doesn't even bother saving the steps as a macro because she's never quite sure what symbols will show up this week. About 4 minutes per import, 50 weeks a year. She quietly hates Mondays.

With this macro she selects the phone column, runs the tool, picks "yes" to keep leading + for international numbers, and 1,200 rows go from messy formatting to clean digits in about 1 second. The popup tells her how many cells got cleaned and how many were skipped (the few that had no digits at all, like a stray "see notes"). She uploads the file to the SMS tool and the reminders go out without a single bounce.

On a typical 1,200-row weekly import, expect about 4 minutes saved per cleanup. Across a year that's about 3 hours back, plus the smaller win of being able to trust the output instead of squinting at column G praying she didn't miss a stray period.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the column of phone numbers (or any range that contains them).
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick CleanPhoneNumbers, click Run.
3. Choose whether to keep a leading + for international numbers. Type Y to keep it (recommended for international lists), anything else to strip everything to plain digits.
4. The macro replaces each cell in place and reports a count of cleaned versus skipped cells.

FAQ

Why does it write the result as text instead of a number?

Phone numbers are not arithmetic. They have leading zeros (think Italian or UK landlines), they're often longer than 15 digits with a country code, and Excel will silently turn 4155550100 into 4.155555E+09 if you let it treat them as numbers. The macro forces text format on each cell so the digits stay exactly as written. Your downstream system (SMS tool, CRM upload, dialer) almost certainly wants text anyway.

What about extensions like "ext 22"?

Extensions get stripped along with everything else. The macro keeps only digits, so 415-555-0100 ext 22 becomes 415555010022. If extensions matter for your data, separate the extension into its own column before running the macro. We may add extension-preservation as an option in a future version if folks ask for it.

Will it merge two numbers in the same cell?

Yes, that's the limitation. If a cell contains 415-555-0100 / 503-555-0200, the result will be 41555501005035550200 — both number's digits concatenated. Most CRMs and contact systems put one phone per cell, so this rarely bites. If your data has multiple numbers per cell, split them first using Excel's Text to Columns feature, then run the macro on each resulting column.

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

No, and that's intentional. 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 the format. If you need validation, use a separate tool or a country-specific lookup. Source code is plain text in the repo; you can add a length check yourself in 5 lines.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running. Always work on a copy of your contact list first if it's the only copy. The macro runs entirely offline. Your phone numbers never leave your machine.