Excel Challenge 23 : Validate Email Formats

What You Will Learn

Five email addresses. Some are valid ("alice@company.com"), some are missing the @ sign ("bob-company.com"), some have @ but no domain ("charlie@"), and some have @ but no period ("diana@corp"). You need to label each one as VALID or INVALID in column C.

Two simple checks: does the address contain an @ symbol AND a period? If both exist, it passes:

=IF(AND(ISNUMBER(SEARCH("@",B2)),ISNUMBER(SEARCH(".",B2))),"VALID","INVALID")

SEARCH looks for the @ symbol — returns a number if found, an error if not. ISNUMBER converts that to TRUE/FALSE. AND requires both checks to pass. SEARCH is case-insensitive, which is correct for email validation — "ALICE@COMPANY.COM" should pass just as easily as "alice@company.com".

Bob's entry "bob-company.com" has a period but no @ — fails the AND check. Charlie's "charlie@" has the @ but no period after the domain — fails. Diana's "diana@corp" has the @ and a period but the period is in the wrong position — actually, it does have both characters, so it passes the format check even though it is not a real email. This is a known limitation of simple format validation.

Alice (alice@company.com) and Ethan (ethan.hunt@mission.net) pass both checks — VALID. Bob has no @, Charlie has no domain, Diana has no period — all INVALID.

SEARCH checks for @ and period. AND requires both. IF labels the result.

The audit formulas:

B9: =COUNTA(B2:B6) — total contacts.
B10: =COUNTIF(C2:C6,"INVALID") — count of invalid emails.

Limitations

This is a lightweight format check, not full RFC validation. It confirms the address has the right shape but does not verify the domain exists or the mailbox is real. "a@b.c" would pass both checks but is unlikely to be a working email. Real-world validation combines a format check with a domain lookup.

SEARCH is used instead of FIND because SEARCH is case-insensitive. If you used FIND("@",B2), it would still work for the @ check since @ has no case, but FIND(".",B2) could behave differently with locale-specific text. SEARCH is the safer choice for general text searching when case does not matter.

SEARCH is case-insensitive, so "ALICE@COMPANY.COM" passes as easily as "alice@company.com". This is correct behavior since email addresses are case-insensitive in practice.