How to Validate Phone Numbers in Excel (Formulas That Work)

To validate phone numbers in Excel, strip every number down to digits, check it against the rules for its country, and flag the rows that fail. The formulas below do that for US and UK numbers and work in Excel for Microsoft 365. But first, stop Excel from damaging the numbers, because it will if you let it.

Excel can only check a number's shape. It doesn't know which prefixes a country has actually allocated, and it can't tell a US mobile from a landline. Where formulas stop, we say so, and show what to use instead.

Step 0: Stop Excel changing your numbers

Excel treats phone numbers as numbers, and that breaks them. Three things go wrong:

What you see	Why it happens
07911123456 becomes 7911123456	Leading zeros are dropped from numbers
+14155552671 becomes 14155552671	A leading + is read as the start of a formula
447911123456 shows as 4.47911E+11	Long numbers are shown in scientific notation

To prevent it:

1. Importing a CSV: use Data → From Text/CSV, choose Transform Data, select the phone column, and set its type to Text. Don't double-click the CSV to open it.
2. Pasting: select the column first, set Home → Number Format → Text, then paste.
3. Typing one number: start with an apostrophe: '+14155552671.

If the zeros are already gone, the safest fix is to go back to the original export and import it again.

Step 1: Strip everything that isn't a digit

Put the raw number in column A. In B2:

=REGEXREPLACE(A2,"[^0-9]","")

That turns (415) 555-2671, 415.555.2671 and +1 415 555 2671 into plain digits. REGEXREPLACE is available in Excel for Microsoft 365. On older versions, chain SUBSTITUTE instead:

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

Step 2: Convert to E.164

E.164 is the international format your dialer, CRM and SMS tool want: a +, the country code, then the number, with no spaces. We explain it in E.164 phone number format.

For a US or Canadian list, in C2:

=LET(d,B2,IF(LEN(d)=10,"+1"&d,IF(AND(LEN(d)=11,LEFT(d,1)="1"),"+"&d,"CHECK")))

Ten digits get +1 added; eleven digits starting with 1 get a +; anything else is flagged.

For a UK list, where national numbers start with 0:

=LET(d,B2,IF(AND(LEN(d)=11,LEFT(d,1)="0"),"+44"&MID(d,2,10),IF(AND(LEN(d)=12,LEFT(d,2)="44"),"+"&d,"CHECK")))

The national 0 is dropped and +44 added. See UK phone number format for why.

Step 3: Check the number's shape

US and Canadian numbers follow the North American Numbering Plan: a 3-digit area code and a 3-digit exchange, neither starting with 0 or 1, then 4 digits. In D2:

=REGEXTEST(C2,"^\+1[2-9]\d{2}[2-9]\d{6}$")

TRUE means the number has the right shape. Without regex functions:

=AND(LEN(C2)=12,LEFT(C2,2)="+1",MID(C2,3,1)>"1",MID(C2,6,1)>"1")

For UK mobiles, which are +447 followed by 9 digits:

=REGEXTEST(C2,"^\+447\d{9}$")

More patterns, and why one regex can't cover every country, are in phone number validation regex.

Step 4: Flag duplicates

Only compare numbers after converting them to E.164, otherwise (415) 555-2671 and +1 415-555-2671 look different. In E2:

=COUNTIF($C$2:$C$10000,C2)>1

TRUE marks a number that appears more than once. To delete the extra copies, select the data and use Data → Remove Duplicates on the E.164 column. We go deeper in removing duplicate phone numbers.

Step 5: Filter and export

Add a filter (Data → Filter), show only rows where the shape check is TRUE and the duplicate flag is FALSE, and copy the E.164 column into your tool. Keep the original column in case you need to trace a number back.

Where Excel formulas stop

Check	Excel formulas	Numbering-plan check	Carrier lookup
Strip punctuation, convert to E.164	Yes, one country at a time	Yes, every country	Yes
Right length and basic shape	Yes	Yes	Yes
Prefix actually allocated	No	Yes	Yes
Country of a mixed list	No	Yes	Yes
Mobile vs landline (outside North America)	No	Yes	Yes
Mobile vs landline (US/Canada)	No	No	Yes
Current carrier	No	No	Yes

A formula will happily pass a number in an area code that has never been assigned, because it has the right shape. Whether an area code is in use, and what kind of line a number is, needs numbering-plan data that Excel doesn't have. None of these checks tells you whether a number is answered today.

The faster way for big lists

For more than a few hundred rows, or any list with more than one country, skip the formulas. In SimpleVerifier:

1. Save the sheet as a CSV, keeping all columns.
2. Upload it on the verifier. The phone column is found automatically; numbers without a country code are read as US numbers.
3. Every row gets phone_status, phone_e164, phone_country and phone_type, checked against the real numbering plan for every country.
4. On the Email + Phone plan ($49.99/month), valid numbers also get a live carrier lookup: the carrier name and the real line type (mobile, landline or VoIP). You get 5,000 lookups a month, and failed lookups don't count.
5. Download the result and open it in Excel with the phone columns set to Text.

The $29.99 Pro plan includes unlimited phone format checks. Using Google Sheets instead? See validating phone numbers in Google Sheets. To format rather than validate, see formatting phone numbers in Excel. And to check a single number right now, use the free phone number validator.