The Problem
Email addresses can look close to valid while still being unusable. A missing @, a missing period, or an incomplete domain is enough to make a contact fail when the list is finally used.
This challenge uses a simple structure check to label each email as VALID or INVALID. It is not a full technical validation of every email rule, but it is a strong first cleanup step for messy contact data.
How We Solve It
The worksheet checks for two basic pieces: an @ symbol and a period. If both are present, the entry passes this simple format test. If either one is missing, the row is marked invalid.
Method 1: Search for the @ symbol
Method 1: Check whether the address contains the @ symbol.
SEARCH locates the @ inside the email text. On its own, this is only part of the rule, but it is a useful first check when diagnosing why a contact is broken.
This solves the first part of the validation problem because an email without an @ is immediately incomplete. It helps the sheet catch one of the most common formatting mistakes before the full rule is applied.
=ISNUMBER(SEARCH("@", B2))
Method 2: Combine the checks with AND
Method 2: Require both the @ symbol and a period in the text.
This is the pattern the solution uses. SEARCH checks for each required character, ISNUMBER converts the search results into TRUE or FALSE, and AND makes sure both tests pass before the row is treated as valid.
This solves the full challenge because the worksheet is using a two-part format rule, not a one-part check. The email only passes when both the @ sign and the period are present, which makes the result more useful than a single-symbol test.
=AND(ISNUMBER(SEARCH("@", B2)), ISNUMBER(SEARCH(".", B2)))
Method 3: Convert the result into labels
Method 3: Turn the logical check into a clear validation label.
The final worksheet needs human-readable output, so IF wraps the logical test and returns VALID or INVALID. That makes the cleanup status easy to read and easy to count in the summary.
This solves the reporting side of the problem. Instead of leaving the result as TRUE or FALSE, the formula creates labels that are easier to scan in the table and easier to count in the invalid-summary cell.
=IF(AND(ISNUMBER(SEARCH("@",B2)),ISNUMBER(SEARCH(".",B2))),"VALID","INVALID")
Function Explanation
1. SEARCH
SEARCH finds the position of text inside another text string. In this challenge it looks for the required symbols inside the email address.
Learn more this functionSEARCH
2. ISNUMBER
ISNUMBER checks whether the search returned a numeric position. That is how the formula turns a successful symbol search into a logical TRUE result.
3. AND
AND requires both symbol checks to pass. It is what keeps the formula from approving an address that only has one of the two required parts.
Learn more this functionAND
This is a simple format check, not a complete email standard validator. It is useful for quick cleanup, but real-world email quality can still need stricter rules than just these two symbols.
Check each email with the simple format rule, label it as valid or invalid, and then complete the summary so the worksheet shows the total number of contacts and how many invalid entries are in the list.