To fix inconsistent phone numbers in a spreadsheet, strip every non-digit character, then reapply one display format, usually (555) 123-4567 for US numbers. In Excel that is a nested SUBSTITUTE formula plus TEXT; in our free cleaning tool it happens automatically with a preview of every change. This guide shows both, plus the edge cases that break the formula approach.
Why phone columns get messy
Phone numbers arrive in a spreadsheet from web forms, CRMs, accounting exports, and human hands, and every source has its own habit. One real customer file can easily contain all of these for the same number:
| As entered | What it is |
|---|---|
| 555.123.4567 | Dots |
| (555) 123 4567 | Parentheses, no hyphen |
| 555-123-4567 | Hyphens |
| 5551234567 | Bare digits |
| 1-555-123-4567 | Leading country code |
| +1 (555) 123-4567 | Full international format |
Sorting does not group them, exact-match lookups fail, and any system you import into will either reject the file or store six formats forever.
The Excel formula approach
Step 1, strip the formatting characters:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),"(",""),")",""),".","")
Step 2, reformat the digits. With the stripped value in B2:
=IF(LEN(B2)=10,TEXT(B2,"(000) 000-0000"),IF(AND(LEN(B2)=11,LEFT(B2,1)="1"),"+1 "&TEXT(RIGHT(B2,10),"(000) 000-0000"),A2))
That handles 10-digit numbers and 11-digit numbers with a leading 1, and passes anything else through unchanged, which is the safe behavior.
Where the formula breaks
- Extensions. “555-123-4567 ext 22” strips to 12 digits and falls through unformatted.
- International numbers. A UK number like +44 20 7946 0958 is valid but is not 10 or 11 digits in US shape. Forcing it into a US mask corrupts it; the formula above correctly leaves it alone, but now your column is still inconsistent.
- Leading zeros and short codes. Five-digit short codes and numbers stored as numeric types (which silently drop a leading zero) need separate handling.
- Text vs. number storage. If Excel stored the column as numbers, formats like (555) are already gone before you start.
The faster path for a whole file
Our free data cleaning tool detects phone columns automatically, standardizes US 10-digit and 1-prefixed 11-digit numbers to (555) 123-4567, and leaves anything it cannot confidently parse exactly as it was, never truncating or guessing. Every change appears in a before/after preview with the old value struck through before anything is applied. Files up to 2MB or 2,500 rows, CSV, free, no account. The full rule set is documented in How Cleaning Works.
FAQ
What is the standard format for US phone numbers?
There is no single legal standard, but (555) 123-4567 is the most common US display convention, and E.164 (+15551234567) is the standard for storage and systems. Pick one per use: display format for humans, E.164 if the data feeds software.
How do I keep Excel from turning phone numbers into scientific notation?
Format the column as Text before pasting data, or import via Data > From Text/CSV and set the column type to Text. Once a number displays as 5.55123E+9 the original digits may already be altered.
Can the free tool handle international numbers?
Not yet. It standardizes US formats and deliberately leaves international numbers untouched rather than corrupting them. International support is on the roadmap, driven by the anonymized format telemetry described in How Cleaning Works.
Leave a Reply