Skip to content
TheSpreadFix

How to Standardize Phone Numbers in Excel

Updated

Phone columns collect every format imaginable: spaces, dashes, brackets, leading zeros, country codes with and without a plus. Here's how to standardise them in Excel with formulas — and where formulas stop being practical.

Target format: E.164

Pick one output format before you start. The safest is E.164: a plus, the country code and the national number with no spaces — +919876543210 for India, +14155552671 for the US, +442079460958 for the UK. It's what most CRMs, dialers and messaging tools expect.

Step 1: stop Excel from changing the numbers

  • Format the phone column as Text (Home → Number format → Text) before pasting or typing, so leading zeros and the + are kept.
  • When opening a CSV, use Data → From Text/CSV and set the phone column's type to Text instead of double-clicking the file.
  • Numbers already shown as 9.18765E+11 have been converted — go back to the source file.

Step 2: strip everything except digits

Remove spaces, dashes, dots and brackets with nested SUBSTITUTE, or in Excel 365 with a single formula:

FormulaNotes
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"(",""),")","")Works in every Excel version; add more SUBSTITUTEs for dots or slashes
=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))Excel 365: keeps digits only

Step 3: add the country code (single-country lists)

For a list where every number is Indian and mobile numbers are 10 digits, take the last 10 digits (which drops a leading 0 or 91) and prefix +91: ="+91"&RIGHT(B2,10) where B2 holds the digits-only value. Check the length first with =LEN(B2) — anything other than 10, 11 (leading 0) or 12 (leading 91) digits needs a manual look.

The same idea works for other countries with fixed-length numbers (the US and Canada: 10 digits, prefix +1).

Where formulas fall short

  • Mixed countries in one column — the RIGHT() trick silently corrupts numbers from other countries.
  • Numbers that already start with + and a country code must be left alone, not re-prefixed.
  • Variable-length numbers (UK, Germany) can't be handled with a fixed RIGHT().
  • Extensions (x42, ext. 42) get merged into the number.
  • Invalid values (“n/a”, “call me”, 12345) pass through unnoticed.

TheSpreadFix's phone normalizer handles these cases: it keeps numbers that already have a + country code, applies country-specific length and trunk-prefix rules, preserves extensions, and lists invalid values instead of changing them.

Frequently asked questions

Why does Excel show my phone numbers as 9.18765E+11?

Excel converted them to numbers. Set the column to Text before importing, or import via Data → From Text/CSV with the column type set to Text.

Should I keep the leading 0?

Not in E.164. The 0 is a national trunk prefix and is replaced by the country code.