Standardize Phone Number Formatting

All versionsSUBSTITUTE

Phone numbers arrive in every format — dots, dashes, parentheses, spaces. Strip everything to digits, then reformat consistently so they sort, match, and dial reliably.

Quick formula: reformat a 10-digit number as (xxx) xxx-xxxx:

=TEXT(VALUE(digits_only), "(000) 000-0000")

Remove all non-digits, then TEXT with a digit-pattern reformats the clean number consistently.

Functions used (tap for the full reference guide):

SUBSTITUTE TEXT

The example

972.555.1234 → (972) 555-1234.

	A	B
1	Raw	Clean
2	972.555.1234	(972) 555-1234

The formula

The formula:

=TEXT(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".",""),"-","")," ",""),"(","")), "(000) 000-0000") // strip, then format

How it works

How it works:

Strip non-digits by nesting SUBSTITUTE to remove dots, dashes, spaces, and parentheses.
Wrap in VALUE to turn the digit string into a number, then TEXT(…, "(000) 000-0000") reformats it.
The 0 placeholders keep leading digits and insert the punctuation.
Keep a digits-only version too — it’s the reliable key for matching and deduping.

Match on digits, display with format. Store the bare 10-digit string for lookups and dedup (so “(972) 555-1234” and “972-555-1234” match), and apply the TEXT format only for display. For variable lengths or extensions, clean to digits first and branch on LEN to choose the format.

Try it: interactive demo

Live demo

Enter a phone number any way.

Formatted:

Variations

Digits-only key

For matching:

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

Dashed format

xxx-xxx-xxxx:

=TEXT(VALUE(digits), "000-000-0000")

Last 4 digits

For display:

=RIGHT(digits, 4)

Pitfalls & errors

Strip every separator. Nest a SUBSTITUTE for each character that appears.

Leading zeros. TEXT with 0 placeholders preserves them; storing as a number alone may drop them.

Extensions/lengths vary. Branch on LEN for non-10-digit numbers.

Practice workbook

📊

Download the free Standardize Phone Number Format practice workbook

A phone-format sheet with the digits-key, dashed, and last-4 variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I standardize phone numbers in Excel?

Strip non-digits with nested SUBSTITUTE, then reformat: =TEXT(VALUE(digits_only), "(000) 000-0000").

How do I match phones in different formats?

Reduce each to a digits-only string and match on that, so (972) 555-1234 equals 972-555-1234.

How do I keep leading zeros?

Use TEXT with 0 placeholders for display; storing as a raw number can drop leading zeros.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: SUBSTITUTE TEXT