Excel email validation formula, for every Excel version and Google Sheets
In short: Formulas for Microsoft 365 (REGEXTEST), any Excel version (FIND/SEARCH, no regex) and Google Sheets (REGEXMATCH, ISEMAIL) for your cell, with a data validation rule and a highlight-invalid rule. Paste your cells to see what each formula returns before you use it. Limit: Every formula here checks the format only. It can't tell whether the domain accepts mail or the mailbox exists.
In Excel for Microsoft 365, =REGEXTEST(A2,"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$") returns TRUE for a well-formed email. Older Excel has no regex, so the classic formula checks for one @, no spaces and a dot after the @. Google Sheets has REGEXMATCH and ISEMAIL. Pick your version, then test it on your own cells.
Price: this tool is free. Checking a whole email list is from $9/mo, and the first 100 checks are free with Google, no card. See pricing
Excel for Microsoft 365 and Excel for the web (REGEXTEST)
=REGEXTEST(A2,"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$")Any Excel version, no regex (Excel 2016, 2019, 2021)
=AND(ISERROR(FIND(" ",A2)),LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1,IFERROR(SEARCH(".",A2,FIND("@",A2)+2)>0,FALSE),LEFT(A2,1)<>"@",RIGHT(A2,1)<>".")Google Sheets (REGEXMATCH)
=REGEXMATCH(A2,"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$")Google Sheets, built-in (ISEMAIL)
=ISEMAIL(A2)
Conditional formatting: colour invalid cells (Excel and Sheets)
=AND($A2<>"",NOT(AND(ISERROR(FIND(" ",$A2)),LEN($A2)-LEN(SUBSTITUTE($A2,"@",""))=1,IFERROR(SEARCH(".",$A2,FIND("@",$A2)+2)>0,FALSE),LEFT($A2,1)<>"@",RIGHT($A2,1)<>".")))Select the column from the first cell down, then Conditional Formatting > New rule > Use a formula (Sheets: Custom formula is).
Runs in your browser. Nothing is uploaded.
Sample result
=REGEXTEST(A2,…) is TRUE for [email protected] and FALSE for [email protected].
- [email protected]
- Classic TRUE, regex FALSE
What this means for you
A formula checks the format only. Whether the mailbox exists needs a verification.
- Microsoft 365, older Excel and Sheets
- Data validation and highlight rules
- Tested on your own cells
- No regex needed for old Excel
- Excel
- REGEXTEST
- ISEMAIL
By SimpleVerifier Team · Last updated · Runs in your browser; nothing you paste is sent to us
The formula says the format is right. It can't say the inbox exists.
- Copy the column of addresses that passed, or export the sheet as CSV.
- Upload or paste it at /verify. The first 100 email checks are free with a Google sign-in, no card.
- For every list after that, Pro includes unlimited email verification. Fair use: the first 100,000 checks a month at full speed, then slower, never cut off.
How do I validate email addresses in Excel with a formula?
- Enter the first cell with an address, such as A2, and whether blank cells should count as valid.
- Copy the formula for your app: Microsoft 365, any Excel version or Google Sheets. Paste it in the row's helper column and fill down.
- To stop bad entries, use the same formula in Data > Data Validation > Custom. To colour bad ones, use the highlight rule in Conditional Formatting.
- Paste a few of your cells into the tester to see what each formula returns, and which addresses pass a formula but would still fail.
Example
- You enter
- Cell A2; tester cells
[email protected],jane [email protected],jane@example,jane@@example.com,[email protected],jane!@exa_mple.com,@example.com,[email protected]. - You get
- The REGEXTEST/REGEXMATCH formula passes 2 of 8 (
[email protected],[email protected]); the classic formula passes 4, because it also accepts[email protected]andjane!@exa_mple.com. The format check agrees with the regex on all 8. - Limits
- Runs in your browser; the formulas are built as you type.
What is the formula to validate an email address in Excel?
In Microsoft 365 and Excel for the web, use REGEXTEST with an address pattern; it returns TRUE or FALSE. In Excel 2016, 2019 and 2021 there are no regex functions, so the classic formula tests the parts with FIND, SEARCH and SUBSTITUTE: no space, exactly one @, at least one character then a dot after the @, not starting with @ and not ending with a dot.
The classic formula is looser. It rejects a space, a missing @ or two of them, but it passes [email protected] and jane!@exa_mple.com, which the regex rejects. The tester shows the difference on your own cells.
How do I validate emails in Google Sheets?
Two ways. ISEMAIL is built in: =ISEMAIL(A2) is TRUE for a value shaped like an address, and Google's page is clear it doesn't check that the address exists. REGEXMATCH with the same pattern as Excel gives you a rule you can read and change. For new entries, Data > Data validation > Text is valid email rejects badly formed input.
In Sheets, the custom rule goes in Data > Data validation > Custom formula is, with the REGEXMATCH formula for the first cell of the range.
Can a formula tell if an email address is real?
No. A formula only sees the text. [email protected] and an address of someone who left the company last year both pass every formula here. Whether the domain accepts mail and the mailbox exists needs a lookup of the domain's mail server and an SMTP check, which is what email verification does.
Use the formula as a first filter for typing mistakes, then verify the column before you send to it.
Which email formula should I use?
| Formula | Works in | Catches | Misses |
|---|---|---|---|
| REGEXTEST with a pattern | Microsoft 365, Excel for the web | Spaces, missing @ or dot, bad characters, one-letter endings | Mailboxes that don't exist |
| Classic FIND/SEARCH | Every Excel version | Spaces, no @ or two @, no dot after the @ | Bad characters, [email protected], mailboxes that don't exist |
| REGEXMATCH with a pattern | Google Sheets | Same as REGEXTEST | Mailboxes that don't exist |
| ISEMAIL | Google Sheets | Values not shaped like an address | Mailboxes that don't exist |
Who uses this tool, and for what?
- Event and form owners
- Put a data validation rule on the email column of a sign-up sheet so a typed address with a space or no dot is refused at entry.
- Sales ops
- Highlight the badly formed addresses in a lead export before it goes to the sequencer.
- Analysts
- Count the well-formed addresses in a column with a helper column and COUNTIF, without a script.
What can’t this tool tell you?
- Every formula here checks the format only. It can't tell whether the domain accepts mail or the mailbox exists.
- The pattern accepts ASCII addresses. Addresses with accented letters in the name are rejected, though a few mail servers accept them.
- REGEXTEST needs Microsoft 365 or Excel for the web; in Excel 2021 and earlier it returns #NAME?.
Common questions
- How do I check if an email is valid in Excel?
- Use =REGEXTEST(A2,"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$") in Microsoft 365, or the classic FIND/SEARCH formula in older versions. Both check the format only.
- Is there an ISEMAIL function in Excel?
- No. ISEMAIL exists in Google Sheets only. In Excel use REGEXTEST (Microsoft 365) or the classic formula from this page.
- How do I use data validation for email addresses in Excel?
- Select the column, open Data > Data Validation, choose Allow: Custom and paste the formula for the first cell. Excel then refuses entries that fail it.
- How do I highlight invalid emails in Excel?
- Select the column, then Home > Conditional Formatting > New Rule > Use a formula, and paste the highlight rule from this page. Cells that fail turn the colour you pick.
- Does the tester send my cells anywhere?
- No. It runs in your browser.
Sources
A tidy list can still be full of dead addresses.
Extracting, deduplicating and syntax checks all work on the text of an address. None of them can tell whether the mailbox exists. Only the server that hosts it can answer that, through an SMTP check, and that is what the paid product does.
- Upload the cleaned file as it is: CSVs up to 100,000 rows
- A real SMTP mailbox check on every row, with catch-all detection
- Small list? Starter at $9/mo covers 2,500 emails a month, cancel anytime
Plans from $9 a month, cancel anytime, no contract. Sign in with Google and your first 100 email checks are free, no card. The free tools stay free either way.