Text
Screen email addresses for common structural problems
Use five spreadsheet checks to flag obvious email-entry mistakes. This heuristic does not establish complete syntax validity, mailbox existence, or deliverability.
Five basic checks for an import-cleanup queue
This example checks one @, no ordinary spaces after trimming, a nonempty local part, a nonempty first domain label, and at least two characters after the final domain dot.
It is a deliberately limited heuristic, not a complete email-address parser. For example, consecutive domain dots and some punctuation still pass; quoted local parts and internationalized addresses need specialized handling. No measured detection percentage is claimed.
A TRUE result means only that these checks passed. Use an appropriate email-validation library for your application’s supported syntax and a confirmation email to verify mailbox access. Do not assume a passing address will receive mail.
| Candidate Email | Failed Rule | Passes these checks? |
|---|---|---|
| maya.chen@arte-soft.com | Passes all 5 rules | TRUE |
| double@@arte-soft.com | 2 "@" symbols (atCnt <> 1) | FALSE |
| maya chen@arte-soft.com | Internal space character | FALSE |
| maya@arte-soft.c | TLD "c" shorter than 2 chars | FALSE |
5-rule email syntax validation formula
Outer IF(AND(atCnt=1, noSp), ..., FALSE) guarantees TEXTBEFORE/TEXTAFTER only run when "@" is present.
=LET(s, TRIM(A2), atCnt, LEN(s) - LEN(SUBSTITUTE(s, "@", "")), noSp, ISERROR(FIND(" ", s)), IF(AND(atCnt=1, noSp), LET(loc, TEXTBEFORE(s, "@"), dom, TEXTAFTER(s, "@"), domName, IFERROR(TEXTBEFORE(dom, "."), ""), tld, IFERROR(TEXTAFTER(dom, ".", -1), ""), AND(LEN(loc)>=1, LEN(domName)>=1, LEN(tld)>=2)), FALSE))Human-readable CRM import audit label ("Passes basic checks" vs "Fix Email")
Wrap IF(..., "Passes basic checks", "Fix Email") around the boolean check for filterable cleanup queues.
=IF(LET(s, TRIM(A2), atCnt, LEN(s) - LEN(SUBSTITUTE(s, "@", "")), noSp, ISERROR(FIND(" ", s)), IF(AND(atCnt=1, noSp), LET(loc, TEXTBEFORE(s, "@"), dom, TEXTAFTER(s, "@"), domName, IFERROR(TEXTBEFORE(dom, "."), ""), tld, IFERROR(TEXTAFTER(dom, ".", -1), ""), AND(LEN(loc)>=1, LEN(domName)>=1, LEN(tld)>=2)), FALSE)), "Passes basic checks", "Fix Email")Try the example
Use the original values or change them to test your understanding.