Flag Invalid Email Addresses

All versionsISNUMBER

Catch obviously broken emails before they bounce. A formula checking for a single @ and a dot after it flags the malformed ones — usable as a helper column or a Data Validation rule.

Quick formula: to test the address in A2:

=AND(ISNUMBER(SEARCH("@", A2)), ISNUMBER(SEARCH(".", A2, SEARCH("@", A2))), LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1)

This checks there’s exactly one @ and at least one dot after it — the core of a valid address shape.

Functions used (tap for the full reference guide):

ISNUMBER SEARCH

The example

Addresses checked for basic structure.

	A	B
1	Address	Valid?
2	ann@dfwexcel.com	TRUE
3	bo@@test	FALSE
4	cy.example.com	FALSE

The formula

A practical structure check:

=AND(LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1, ISNUMBER(SEARCH(".", A2, SEARCH("@", A2)))) // exactly one @, and a dot after it

How it works

Three quick conditions catch most bad addresses:

Exactly one @: LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1 counts the @ signs by measuring how much the length shrinks when they’re removed.
A dot after the @: SEARCH(".", A2, SEARCH("@", A2)) looks for a period starting after the @ — the domain needs one.
ISNUMBER turns each found-position into TRUE; AND requires all conditions to pass.
Use it as a helper column, or paste it into Data Validation → Custom to block bad entries as they’re typed.

This is a shape check, not full validation. Real email rules (RFC 5322) are far more complex, and a structurally valid address can still be undeliverable. For data entry this catches the common mistakes; for deliverability you need to actually send or verify.

Try it: interactive demo

Live demo

Type an email; see whether it passes the shape check.

Valid shape:

Variations

Just check for @

The minimal test:

=ISNUMBER(SEARCH("@", A2))

As a Data Validation rule

Data → Data Validation → Custom:

=AND(LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1, ISNUMBER(SEARCH(".",A2,SEARCH("@",A2))))

Flag text for review

Label instead of TRUE/FALSE:

=IF(ISNUMBER(SEARCH("@",A2)), "OK", "Check")

Pitfalls & errors

Not RFC-complete. The check verifies shape (one @, a dot in the domain), not true validity. Some valid addresses are unusual; some invalid ones pass.

SEARCH needs ISNUMBER. SEARCH returns a position or an error, not TRUE/FALSE. Wrap each SEARCH in ISNUMBER inside the AND.

Leading/trailing spaces fool it. Run TRIM on the input first if addresses may have stray spaces.

Practice workbook

📊

Download the free Flag Invalid Email Addresses practice workbook

An email-check sheet with the full and minimal tests, a Data Validation rule, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I check if an email address is valid in Excel?

Test the structure: =AND(LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1, ISNUMBER(SEARCH(".",A2,SEARCH("@",A2)))) checks for exactly one @ and a dot after it.

Can I block invalid emails as they're typed?

Yes — put the same formula in Data → Data Validation → Custom so Excel rejects malformed entries on input.

Does this guarantee the email is real?

No. It only checks the address shape. A structurally valid address can still be undeliverable; true verification requires sending or an email-verification service.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: ISNUMBER · SEARCH