The Problem
A phone list may look fine at first glance, but small issues can make it useless for calls or SMS. Some numbers are too short, some are too long, and some contain letters that slipped in during entry.
When that happens, the list needs more than a visual check. This challenge is about turning simple validation rules into formulas so the sheet can flag bad numbers and count how many rows need attention.
How We Solve It
For this worksheet, a number is valid only when two things are true. It must have exactly 10 characters, and it must be fully numeric. We check those conditions row by row, then summarize the invalid results at the bottom.
Method 1: Test the length with LEN
Method 1: Count how many characters are in the phone value.
LEN gives us the character count for each phone value. That quickly reveals rows that are missing digits or include extra ones. It does not prove the value is clean, but it gives us the first half of the validation rule.
This solves the length side of the problem because the worksheet expects a fixed 10-digit pattern. Any entry that is shorter or longer can be rejected immediately before we even check whether the characters are valid.
=LEN(B2)
Method 2: Check whether the value is numeric
Method 2: Strip separators and test whether the remaining text can be read as a number.
A value can still have 10 characters and be wrong if it includes letters. That is why the second check matters. Wrapping the entry in VALUE and testing it with ISNUMBER tells us whether Excel can read the content as a real number.
This solves the content side of the problem by separating numeric entries from ones that only look like phone numbers. A row such as 123ABC7890 can pass a character count, but it fails once Excel tries to treat it as a real number.
=ISNUMBER(VALUE(B2))
Method 3: Combine both rules in one formula
Method 3: Combine both checks so each row gets a clear status label.
The finished formula uses AND so both tests must pass. If the length is 10 and the content is numeric, the row is marked VALID. Otherwise it is marked INVALID. That keeps the output easy to scan and easy to count.
This solves the full challenge because it turns both validation rules into one final decision. The sheet does not just measure the phone number anymore. It gives each row a clear status that can be reviewed, filtered, and summarized at the bottom.
=IF(AND(LEN(B2)=10,ISNUMBER(VALUE(B2))),"VALID","INVALID")
Function Explanation
1. LEN
LEN counts characters in a text string. Here it helps us catch phone numbers that are too short or too long for the rule we are using.
Learn more this functionLEN
2. ISNUMBER
ISNUMBER checks whether a result is numeric. In this challenge it works with VALUE to reject entries that contain letters or other invalid characters.
3. COUNTIF
COUNTIF counts how many rows end up with the label INVALID. That gives the summary cell at the bottom without needing another manual check.
This kind of rule is useful for a tightly controlled format. If your real data includes country codes, spaces, or dashes, you would usually clean those first and then run the validation.
Mark each phone number as VALID or INVALID based on length and numeric content, then finish the summary so the sheet shows how many records were checked and how many bad numbers were found.