How to standardize phone numbers in a spreadsheet

A phone column built from more than one source — a web form, a manual entry, an old export — tends to mix separators, country codes, and spacing. Formulas can fix the common cases, and it is worth knowing which cases those are, because the exceptions arrive faster than most people expect on a real contact list.

What a messy phone column actually looks like

Before reaching for a formula, look at the range of what is in the column. This is a representative sample of a mixed Australian list, and every row needs different handling:

Six valid numbers of four different lengths, plus one that only looks like a phone number.

That last row is the reason formatting alone is not enough. A formula happily reformats an invalid number into something that looks correct, and you find out it was never dialable when a campaign bounces.

Strip everything but digits first

Whatever the final format, start by reducing every entry to its digits, so you are formatting from a clean base rather than patching around existing punctuation:

That strips spaces, hyphens, and parentheses. Dots and slashes are common too, and each needs another SUBSTITUTE() layer wrapped around the last. If the list came from a web form, add one for CHAR(160) as well — a non-breaking space looks exactly like a space and survives every formula above.

Reapply consistent formatting

Once you have a clean digit string, a nested formula can reinsert your preferred spacing. For a 10-digit Australian mobile:

This holds as long as every number in the column has the same digit count and structure — which, looking at the table above, is true of almost no real column. Applied indiscriminately it produces this:

One formula, one correct answer out of three — and all three outputs look plausible.

The fix is a branch on length, which is where the formula stops being a one-liner:

And length alone still cannot separate a mobile from a landline, since both are ten digits — that needs the prefix as well. Each additional case makes the formula harder to read and no more able to tell you whether the number is real.

Handle mixed country-code formats

A column mixing international and national formats needs a conditional check before any formatting can apply consistently:

From there you can branch — but converting between the two requires knowing the country's dialling plan, specifically its country code and its national trunk prefix. For Australia, converting international to national means dropping +61 and adding a leading 0. That rule is not portable:

The "add a leading zero" step that works for AU and UK is wrong for the US and Italy.

Where the formula approach genuinely runs out is narrower than "more than one country", and worth stating precisely. Every value in the table above carries its country code, so each one says which country it belongs to and a formula can branch on that: read the code, apply that country's rule, done. Tedious to write and maintain, but possible.

What defeats it is a column of national-format numbers from more than one country, with no country code on the value and no country column beside it. 020 7946 0018 and 0412 345 678 are both plausible national numbers, and nothing in either string says which numbering plan to read it under. At that point the information needed to standardize the column is not in the column, and no amount of string manipulation puts it there — you have to recover the country from somewhere else, or accept that the values cannot be made comparable.

Watch for numbers a spreadsheet has already damaged

If the column was ever formatted as Number or General rather than Text, some values were changed before you opened the file:

Unlike a truncated postcode, a stripped phone number usually can be rebuilt — the missing digit is always the same one.

An Australian mobile always begins 04 and always has ten digits, so a nine-digit value beginning 4 is unambiguously missing its leading zero and can be repaired:

Prevention is better: format phone columns as Text before pasting into them, and import CSVs via Data → From Text/CSV with the column type set to Text rather than double-clicking the file.

Common questions

What format should I standardize to? Match whatever the column already mostly uses, unless something downstream requires otherwise. If you are feeding a system that dials or sends SMS, E.164 — +61412345678, no spaces — is the format built for machines and the one least likely to be misread.

Should I keep the leading zero or the country code? Not both, and not neither. +61412345678 and 0412 345 678 are each complete; +610412345678 is a number that does not exist and is a common result of gluing the two conventions together.

Why does my phone column sort strangely? Because it is a mix of text and numbers. Values Excel parsed as numbers sort before or after the ones it kept as text, regardless of their digits. Converting the whole column to Text fixes the sort.