Flag Invalid Email Addresses

Excel Formulas › Data Cleaning

All versionsISNUMBER

Catch malformed emails before they cause bounces. A formula can check the basics — exactly one @, a dot after it, and no spaces — flagging entries that need a human look.

Quick formula: basic email validity check:
=AND(ISNUMBER(SEARCH("@", A2)), ISNUMBER(SEARCH(".", A2, SEARCH("@", A2))), ISERROR(SEARCH(" ", A2)))
Has an @, has a dot after the @, and contains no space — the basic structural test.

Functions used (tap for the full reference guide):

ISNUMBERSEARCH

The example

[email protected] passes; name@site fails.

AB
1EmailValid?
2[email protected]TRUE
3a@bFALSE

The formula

The formula:

=AND(ISNUMBER(SEARCH("@",A2)), ISNUMBER(SEARCH(".",A2,SEARCH("@",A2))), ISERROR(SEARCH(" ",A2))) // @, dot after, no space

How it works

How it works:

  1. ISNUMBER(SEARCH("@", A2)) confirms there’s an @ sign.
  2. SEARCH(".", A2, SEARCH("@", A2)) checks for a dot after the @ (a domain extension).
  3. ISERROR(SEARCH(" ", A2)) ensures there’s no space.
  4. AND combines them — TRUE means structurally plausible, not guaranteed deliverable.

Format ≠ deliverable. This catches obvious typos (missing @, no domain, stray spaces) but can’t verify the address actually exists or accepts mail — that needs real verification. Use the formula to flag the clearly-broken entries for review, not to certify a list as valid.

Try it: interactive demo

Live demo

Type an email.

Valid format:

Variations

Exactly one @

No doubles:

=LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1

Flag for review

Readable result:

=IF(valid_test, "OK", "Check")

Domain part

After the @:

=MID(A2, SEARCH("@",A2)+1, 99)

Pitfalls & errors

Structural only. Passing the test doesn’t mean the address works.

One @ check. Add the LEN-SUBSTITUTE test to reject double @.

SEARCH is case-insensitive. Fine here — use FIND if case matters elsewhere.

Practice workbook

📊
Download the free Validate Email Format practice workbook
An email-validation sheet with the one-@, flag, and domain-part variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I validate an email format in Excel?
Check the basics with =AND(ISNUMBER(SEARCH("@",A2)), ISNUMBER(SEARCH(".",A2,SEARCH("@",A2))), ISERROR(SEARCH(" ",A2))).
Does this confirm the email works?
No — it only catches structural problems like a missing @ or domain. Real deliverability needs verification.
How do I reject emails with two @ signs?
Use =LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1 to require exactly one.

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

Related formulas: ISNUMBER & ISTEXT · Check if contains · Extract email domain

Function references: ISNUMBERSEARCH