Email addresses
How to Validate Email Addresses in Excel (Formulas, Typos and What They Miss)
A spreadsheet can check an email's shape: one @, something before and after it, a dot in the domain, no spaces. It cannot prove the mailbox exists. For the shape check use =AND(ISNUMBER(FIND("@",A2)),ISNUMBER(FIND(".",A2,FIND("@",A2))),ISERROR(FIND(" ",A2)),LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1); TRUE means it looks valid. To catch typos in well-known domains, list the domains in use and look for near-misses.
A list full of bad addresses causes bounces, which hurt your sender reputation, and silently loses the people whose addresses were mistyped. Checking the list before you send is cheap. The trick is knowing what a check can and cannot do.
Step 1: clean the text first
Many "invalid" addresses are valid ones with stray spaces or capital letters. Fix those before testing:
=LOWER(TRIM(A2))Email addresses are not case sensitive in practice, so lowercase is safe and makes duplicates easy to find. See removing extra spaces if TRIM does nothing, because the spaces may be non-breaking.
Step 2: check the shape with a formula
Put this in a helper column next to the cleaned addresses:
=AND(ISNUMBER(FIND("@",B2)),ISNUMBER(FIND(".",B2,FIND("@",B2))),ISERROR(FIND(" ",B2)),LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))=1,LEFT(B2,1)<>"@",RIGHT(B2,1)<>".")It returns TRUE only when the address has exactly one @, a dot somewhere after the @, no spaces, does not start with @ and does not end with a dot. Filter the column to FALSE to see everything that needs attention.
Warning: this check is deliberately simple. It accepts a@b.c and rejects some rare but legal addresses. Treat TRUE as "plausible", never as "confirmed".
Step 3: catch typos in the domain
The most common mistakes are in the part after the @: gmial.com, gmai.com, hotmial.com, yaho.com, gmail.con. Extract the domain into its own column:
=MID(B2,FIND("@",B2)+1,100)Then build a list of unique domains (a pivot table on that column works) sorted by count. The real providers appear at the top with large counts; typos appear as small counts that look almost the same as a big one. The full method, including a replacement table, is in fixing email typos in Excel.
What no spreadsheet check can tell you
- Whether the mailbox exists.
sam@example.comhas a perfect shape and may not be a real inbox. - Whether a typo is a typo. A domain you do not recognise may be a real company.
- Whether the person still reads it. Old addresses stay valid and unread.
Only sending (or a verification service that contacts the mail server) can confirm an address, so use the spreadsheet to remove what is clearly broken and to catch the typos you can be sure about, and keep a bounce process for the rest. More on that in cleaning an email list before sending.
Free quick check in your browser
For a short list, paste it into the free email checker. It fixes the typos it can be sure about, lists what it cannot, and runs in your browser, so the addresses are not uploaded.
A whole file at once
DecodingPro trims and lowercases addresses, fixes mistyped domains when it is certain, flags broken addresses such as a missing @ rather than inventing one, and finds duplicates, all in one pass with every change logged.
You start with 100 free credits. Every change is logged, and anything unclear is flagged, never guessed.
Fix a spreadsheetQuestions people ask
Can Excel check if an email address really exists?
No. Excel can only check the format. Confirming that a mailbox exists needs a message to the mail server, which is what verification services and the act of sending do.
How do I find emails without an @ sign in Excel?
Use =ISNUMBER(FIND("@",A2)) in a helper column and filter for FALSE. Do not guess a missing @: a cell like sam.example.com has no safe fix and is better corrected from the source.
Should I lowercase email addresses?
Yes, for cleaning and de-duplicating it is safe in practice, because mail systems treat the domain as case-insensitive and almost all treat the name part the same way.
How do I find duplicate email addresses?
Clean them with LOWER(TRIM()) first, then use Remove Duplicates or conditional formatting on that column. See the guide on finding and highlighting duplicates in Excel for the steps.