Excel Challenge 27 : Detect Invalid Phone Numbers

What You Will Learn

Five phone numbers. Some are valid (10 digits, all numbers), some are not. Bob has 7 digits (too short), Charlie has 11 (too long), Diana has letters (123ABC7890). Only Alice (1234567890) and Ethan (9998887776) are valid.

Two conditions must both pass: exactly 10 characters AND fully numeric. AND checks both at once:

=IF(AND(LEN(B2)=10,ISNUMBER(VALUE(B2))),"VALID","INVALID")

Breaking it down:

LEN(B2)=10 — counts characters. Bob's 5551212 returns 7 — fails. Charlie's 12345678901 returns 11 — fails.
ISNUMBER(VALUE(B2)) — checks whether the content can be read as a number. Diana's 123ABC7890 contains letters — fails.
AND(...) — both must be true. If either check fails, the label is INVALID.

ISNUMBER alone is not enough — "1234567890" passes, but so would "12345678901" (11 digits). LEN alone is not enough either — "123ABC7890" has 10 characters but includes letters. They need each other.

Two checks: length 10 AND fully numeric.

The audit formulas:

B9: =COUNTA(A2:A6) — total records.
B10: =COUNTIF(C2:C6,"INVALID") — count of invalid numbers.

The VALUE function converts a text string that looks like a number into an actual number. "1234567890" becomes 1234567890 (numeric), but "123ABC7890" cannot be converted because it contains letters — VALUE returns an error. ISNUMBER then returns FALSE for that error, which AND uses to mark the row as INVALID.

Three invalid numbers out of five. The validation is strict — a single letter in an otherwise correct 10-digit string still fails.

Going Further: Cleaning Before Validation

If your data contains dashes, spaces, or parentheses, strip them first before running length and numeric checks:

=IF(AND(LEN(SUBSTITUTE(B2,"-",""))=10,ISNUMBER(VALUE(SUBSTITUTE(B2,"-","")))),"VALID","INVALID")

SUBSTITUTE removes the dashes, then the checks run on the cleaned value. This is useful for data that mixes formats like "123-456-7890".

One important detail: ISNUMBER(VALUE(B2)) works when B2 contains only digits, but it returns an error for text with letters or symbols. The error propagates through AND as FALSE, which means the IF formula correctly marks it as INVALID. Without the VALUE wrapper, ISNUMBER would misclassify text like "123" as FALSE because Excel stores it as text, not a number.

This validation assumes exactly 10 digits with no formatting. If your real data includes country codes or dashes, clean the input first or adjust the length check to match your expected format.