How to Validate Phone Numbers in Google Sheets (Formulas + Limits)

Google Sheets can clean and shape-check phone numbers with REGEXREPLACE and REGEXMATCH: strip the formatting, convert US numbers to E.164, flag the ones with the wrong length, and catch duplicates. It can't truly validate them, because it doesn't know which area codes exist or what type of line a number is. Below are formulas you can paste, then where they stop.

Assume raw numbers are in column A, starting at row 2.

First: stop Sheets eating the plus sign

Sheets treats a cell starting with + as a formula, so +14155552671 turns into the number 14155552671, and long numbers can switch to scientific notation. Before pasting or typing numbers:

1. Select the column.
2. Format → Number → Plain text.

Or start each entry with an apostrophe: '+14155552671. Do this for every column that holds phone numbers, including the formula output columns below if you'll paste their values elsewhere.

Step 1: Strip everything that isn't a digit

In B2:

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

(415) 555-2671, 415.555.2671 and +1 415 555 2671 become 4155552671 or 14155552671. Extensions and second numbers in the same cell get glued on too, so look at anything longer than 11 digits by hand.

Step 2: Convert US numbers to E.164

In C2:

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

Ten digits get +1 in front; eleven digits starting with 1 just get a +. Anything else is flagged CHECK. This formula is US/Canada only: applying it to a UK or Indian number produces a wrong number, not an error. More on why in E.164 format explained.

Step 3: Check the North American shape rules

In D2:

=IF(C2="CHECK", FALSE,
  REGEXMATCH(C2, "^\+1[2-9][0-9]{2}[2-9][0-9]{6}$"))

That enforces the two rules a regex can: the area code and the exchange code each start with 2–9. (212) 123-4567 fails; (415) 555-2671 passes.

What it can't check:

• whether the area code is assigned. (555) 123-4567 passes the regex but 555 isn't an area code;
• N11 service codes, toll-free vs local, Canadian vs US vs Caribbean;
• whether the number is mobile or landline. See mobile vs landline lookup.

Step 4: Flag duplicates

In E2:

=IF(COUNTIF(C$2:C2, C2)>1, "DUPLICATE", "")

This marks the second and later copies of each E.164 number, so the first stays unflagged. Because step 2 normalised the format, (415) 555-2671 and 415.555.2671 are now caught as the same number. To delete them, copy column C, paste values only, then use Data → Data cleanup → Remove duplicates.

Step 5: Catch placeholders

Filler values pass shape checks. Add a check for the usual suspects:

=OR(RIGHT(B2,10)="1234567890",
  LEN(SUBSTITUTE(RIGHT(B2,10), LEFT(RIGHT(B2,10),1), ""))=0)

That catches 1234567890 and ten of the same digit, such as 0000000000 or 5555555555. (Sheets' regular expressions don't support backreferences, which is why this uses SUBSTITUTE instead.) It won't catch everything a bored form-filler types.

Where formulas stop

Need	Sheets formulas	Numbering-plan validator
Strip formatting	Yes	Yes
US to E.164	Yes	Yes
International to E.164	Painful, per-country	Yes
Area code actually assigned	No	Yes
Country of a +1 number (US, Canada, Jamaica…)	No	Yes
Line type (mobile, landline, toll-free, VoIP)	No	Yes, by range
In service right now	No	No. Needs a carrier lookup

For international lists or anything past a few hundred rows, formulas cost more time than they save.

The faster route for a whole sheet

1. File → Download → Comma-separated values (.csv).
2. Upload the CSV on the SimpleVerifier verifier. Each row gets phone_status, phone_e164, phone_country and phone_type. Numbers without a country code are read as US numbers.
3. File → Import the result back into Sheets, with the phone columns formatted as plain text.

If the sheet also has emails, they get a live mailbox check in the same upload. Phone validation is included in the $29.99 plan. For the email column on its own, there's also a guide to verifying emails in Google Sheets.

Related: clean phone numbers in a CSV · US phone number format