Excel Challenge 20: Validate Email Formats

Challenge Scenario

In web application user onboarding, typographical errors in email inputs (such as omitting the @ or forgetting the top-level domain dot .) cause immediate verification delivery failures.

As a Data Integrity Specialist, you are auditing user account records (Column B):

Syntax Status (Column C): If the email contains both an "@" and a "." character, output "VALID", otherwise output "INVALID".
Total Registrations (Cell B8): Tally total registered user accounts using COUNTA.
Valid Count (Cell C8): Count verified valid email formats using COUNTIF.
Invalid Count (Cell D8): Count rejected invalid email formats using COUNTIF.

Your Sheet Layout

Before writing the syntax validation formula, review the organizational structure of this verification sheet. Setting up separate zones ensures that raw user account data remains intact while providing real-time auditing and aggregate compliance metrics.

The layout is divided into three key operational zones:

User Directory Zone (Columns A & B): Contains user names and submitted raw email addresses requiring audit.
Validation Status (Column C): Uses logical text functions to verify whether both required symbols (@ and .) are present.
Audit KPI Summary (Rows 7–8): Counts total registered accounts, tallies verified valid emails, and tracks invalid submission volumes.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:A5`	User Profile	Data (Account Info)	Raw text names	Read-only user profile directory names.
`B2:B5`	Registered Email	Data (Source Email)	Raw email strings	Source email inputs to be validated for proper formatting.
`C2:C5`	Syntax Status	Check	`=IF(AND(ISNUMBER(SEARCH("@", B2)), ISNUMBER(SEARCH(".", B2))), "VALID", "INVALID")`	Enter the dual-check validation formula in C2 and copy down to C5.
`B8`	Total Emails	Summary (Total Count)	`=COUNTA(A2:A5)`	Count total user accounts in the directory.
`C8`	Valid Format Count	Summary (Passed Tally)	`=COUNTIF(C2:C5, "VALID")`	Count how many email entries passed validation with "VALID".
`D8`	Invalid Format Count	Summary (Failed Tally)	`=COUNTIF(C2:C5, "INVALID")`	Count how many email entries failed validation with "INVALID".

This layout creates a clear validation pipeline, enabling automated quality checks without altering original customer records.

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), output "VALID" if email contains both "@" and ".", otherwise "INVALID".

Objective #2

Cell: B8

In cell B8, count total emails using COUNTA.

Objective #3

Cell: C8

In cell C8, count "VALID" emails using COUNTIF.

Objective #4

Cell: D8

In cell D8, count "INVALID" emails using COUNTIF.

Solution & Formula Breakdown

Validating email structure is essential to prevent bounced messages and bad database entries. Let's break down how this formula detects invalid emails.

1. Dual-Condition Validation with SEARCH, ISNUMBER, and AND (C2:C5)

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

Here is how the formula evaluates each email step by step:

SEARCH("@", B2): Searches for the @ symbol in cell B2. If found, it returns its character position (a number like 6). If not found, it returns a #VALUE! error.
ISNUMBER(...): Returns TRUE if a number position was returned, and FALSE if SEARCH resulted in an error.
AND(...): Combines both tests. It returns TRUE only if BOTH the @ test AND the . test return TRUE.
IF(...): If the AND condition is TRUE, it returns "VALID". Otherwise, it returns "INVALID".

2. Row-by-Row Evaluation Trace

Row 2 ("alice@acme.com"): Contains both "@" (pos 6) and "." (pos 11) → "VALID".
Row 3 ("bob_vance_at_refrig.com"): Missing "@" → SEARCH returns error → "INVALID".
Row 4 ("charlie@paddys"): Missing "." → SEARCH returns error → "INVALID".
Row 5 ("diana.p@themyscira.gov"): Contains both "@" (pos 8) and "." (pos 6 & 19) → "VALID".

3. Aggregating Quality Metrics (B8, C8, D8)

Cell B8 (Total Accounts): =COUNTA(A2:A5) → Evaluates to 4.
Cell C8 (Valid Count): =COUNTIF(C2:C5, "VALID") → Evaluates to 2.
Cell D8 (Invalid Count): =COUNTIF(C2:C5, "INVALID") → Evaluates to 2.