How to Standardize Phone Number Formats in Excel

Turn phone numbers typed as 555.330.1189, 5556142290 or +1 (555) 902-5513 into one consistent (555) 123-4567 format.

You will learn to

• Format a 10-digit number as (000) 000-0000 with TEXT
• Strip separators and convert to a number first
• Handle mixed formats and a +1 country code in one formula

How it works

When a phone number is stored as a plain 10-digit number, =TEXT(B2,"(000) 000-0000") formats it as "(555) 614-2290". Each 0 is a digit, filled from the right, and the brackets, space and dash are added as literal characters. Use "000-000-0000" if you prefer dashes.

Typed-in numbers are usually text with punctuation, such as "555.330.1189" or "+1 (555) 902-5513". Strip the separators with nested SUBSTITUTE, keep the last ten digits with RIGHT to drop a leading country code, then convert the result with VALUE (or --) so TEXT can format it. The result is text, which is what you want for phone numbers.

=TEXT(B2,"(000) 000-0000")