Excel Challenge 21: Detect Invalid Phone Numbers

Challenge Scenario

In CRM administration and SMS marketing campaigns, sending automated text notifications requires valid 10-digit standard phone numbers. Messy lead lists often contain corrupted entries with missing digits or extra characters.

Before launching an outbound SMS campaign, data analysts must validate every contact record by counting the character length of the phone string and flagging invalid entries.

As a Marketing Data Quality Assistant, your objectives are:

Digit Count (Column C): Measure the exact character length of the phone string using the LEN function.
Validity Status (Column D): If Digit Count equals exactly 10 (LEN(B2) = 10 or C2 = 10), assign "VALID"; otherwise assign "INVALID".
Total Records (Cell B8): Count total customer records in the table using COUNTA.
Valid / Invalid Counts (Cells C8 & D8): Tally how many phones are "VALID" and "INVALID" using COUNTIF.

Your Sheet Layout

The phone number validation sheet separates raw customer contacts from length validation checks and campaign audit rollups:

Customer Contacts (Columns A & B): Shows customer names and unformatted raw phone number text strings.
Length & Validity Checks (Columns C & D): Measures character count with LEN and classifies each row as VALID or INVALID.
Campaign Audit Summary (Rows 7 & 8): Computes total customer count and splits the total into valid vs invalid phone totals.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:B5`	Customer Name, Phone Raw String	Customer Contacts	Raw unformatted phone number strings	Read-only incoming customer contact records.
`C2:C5`	Digit Count	Validation	`=LEN(B2)`	Calculates the number of characters in the phone string.
`D2:D5`	Validity Status	Validation	`=IF(C2 = 10, "VALID", "INVALID")`	Outputs "VALID" if length is exactly 10, else "INVALID".
`B8`	Total Records	Audit Summary	`=COUNTA(A2:A5)`	Counts total customer rows in the dataset (4).
`C8`	Valid Phone Count	Audit Summary	`=COUNTIF(D2:D5, "VALID")`	Counts how many phone numbers are valid 10-digit numbers (2).
`D8`	Invalid Phone Count	Audit Summary	`=COUNTIF(D2:D5, "INVALID")`	Counts how many phone numbers failed the 10-digit check (2).

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: C2-C5

In Column C (C2:C5), calculate phone string length using LEN(B2).

Objective #2

Cell: D2-D5

In Column D (D2:D5), output "VALID" if length equals 10, otherwise "INVALID".

Objective #3

Cell: B8

In cell B8, count total customer records using COUNTA(A2:A5).

Objective #4

Cell: C8-D8

In cell C8, count "VALID" phone numbers, and in cell D8 count "INVALID" numbers using COUNTIF.

Solution & Formula Breakdown

Let's examine how the string length inspection and validation formulas work step by step:

1. Measuring Phone String Length with LEN (C2:C5)

=LEN(B2)

The LEN(text) function returns the exact number of characters in a cell:

Row 2 (Alice Johnson - "5550192834"): Has 10 characters → outputs 10.
Row 3 (Bob Smith - "5550192"): Has only 7 characters → outputs 7 (incomplete number).
Row 4 (Charlie Brown - "55501827364"): Has 11 characters → outputs 11 (too long).
Row 5 (Diana Prince - "5550123948"): Has 10 characters → outputs 10.

2. Determining Status with IF (D2:D5)

=IF(C2 = 10, "VALID", "INVALID")

The IF function checks whether the character count equals 10:

If C2 = 10 is TRUE, return "VALID".
If FALSE, return "INVALID".
Rows 2 and 5 receive "VALID"; Rows 3 and 4 receive "INVALID".

3. Audit Summary Metrics (B8, C8, D8)

Cell B8 (Total Records): =COUNTA(A2:A5) returns 4.
Cell C8 (Valid Phone Count): =COUNTIF(D2:D5, "VALID") returns 2.
Cell D8 (Invalid Phone Count): =COUNTIF(D2:D5, "INVALID") returns 2.

You can also combine both functions directly into one cell: =IF(LEN(B2)=10, "VALID", "INVALID"). This is widely used in data cleaning pipelines to sanitize contact databases in a single step!