How to Verify Email Addresses in Google Sheets: Tested Formulas

Google Sheets can clean an email list, remove duplicates and reject addresses that are badly formed, but it cannot tell you whether a mailbox exists. For that you export the cleaned column, run it through a verifier that checks MX records and talks to the mail server, then pull the results back into the sheet with XLOOKUP.

This guide covers both halves, with formulas we tested against a deliberately messy file of 19 rows, and the places where the usual advice goes wrong.

What Sheets can and cannot tell you

Sheets is good at text and blind to the network. Everything below the line in this table needs a DNS query or an SMTP connection, and formulas have neither.

Check	Possible in Sheets?	How
Stray spaces, capitals	Yes	TRIM, LOWER, SUBSTITUTE
Duplicates	Yes	Data cleanup, or UNIQUE on a normalised column
Badly formed address	Yes	ISEMAIL or REGEXMATCH
Domain can receive mail (MX)	No	DNS lookup
Mailbox exists	No	SMTP RCPT TO conversation
Catch-all, disposable, role address	Partly	Role prefixes by formula; the rest needs lookups

The reason is structural. Apps Script, the scripting layer behind Sheets, reaches the network through UrlFetchApp, which "can use the URL Fetch service to issue HTTP and HTTPS requests". A mailbox check is not an HTTP request: it is a conversation with the recipient's mail server on port 25, the same one described in how to check an email is valid without sending. Any add-on that claims to verify inside Sheets is sending your addresses to an outside service over HTTP. That is fine, but know that it is happening.

Step 1: Normalise the column

Put a cleaned copy of every address in a helper column before doing anything else. Most duplicate and syntax problems are whitespace and capitalisation.

If your addresses are in column A, put this in B2 and fill down:

=LOWER(TRIM(SUBSTITUTE(A2, CHAR(160), " ")))

The SUBSTITUTE part matters more than it looks. Google's own Data cleanup help page notes that "Non-breaking space, like &nbsp, will not be trimmed." Addresses copied from a website or a CRM screen often carry one. It is invisible in the cell, so ana@example.com and ana@example.com plus a hidden character look identical and are treated as different values. Converting CHAR(160) to an ordinary space first lets TRIM remove it.

Lower-casing is safe in practice. Strictly, RFC 5321 says the part before the @ "MUST BE treated as case sensitive", but it adds that exploiting this "impedes interoperability and is discouraged", and mainstream providers do not issue mailboxes that differ only by case.

Step 2: Check syntax with ISEMAIL or REGEXMATCH

ISEMAIL is the quick option; REGEXMATCH lets you see and control the rule. Neither checks that the address exists.

Google describes ISEMAIL as checking whether a value follows "a commonly accepted format for email addresses", and states plainly that it does not verify the address exists. It does not publish the exact rule, so you cannot predict how it treats edge cases without testing them.

If you want the rule in front of you, use REGEXMATCH, which uses Google's RE2 engine. In C2:

=REGEXMATCH(B2, "^[a-z0-9_%+'-]+(\.[a-z0-9_%+'-]+)*@[a-z0-9]([a-z0-9-]*[a-z0-9])?(\.[a-z0-9]([a-z0-9-]*[a-z0-9])?)*\.[a-z]{2,}$")

It runs on the lower-cased column, so it only needs lower-case ranges. It rejects a dot at the start or end of the local part, consecutive dots, hyphens at the edge of a domain label, a missing top-level domain and anything with a space.

What the pattern did on our test file

We built a 19-row file of the mistakes that turn up in real exports and ran the normalise-then-match logic over it on 1 October 2026. Because Sheets formulas cannot be scripted from outside, we re-implemented them in Python using only regex features that RE2 shares with other engines; the test log and input file are in our research notes.

Raw value	After Step 1	Pattern result
Ana.Silva@Example.com	ana.silva@example.com	Pass
ana.silva@example.com + non-breaking space	ana.silva@example.com	Pass
ben@example	unchanged	Fail: no TLD
cara@@example.com	unchanged	Fail
dev@example..com	unchanged	Fail
eve@example,com	unchanged	Fail: comma
mailto:finn@example.com	unchanged	Fail
gus.@example.com	unchanged	Fail: trailing dot
hana o@example.com	hana o@example.com	Fail: space
o'brien@example.co.uk	unchanged	Pass
jo+news@example.com	unchanged	Pass
max@-example.com	unchanged	Fail
ola@example.com;ola2@example.com	unchanged	Fail: two addresses
Pia <pia@example.com>	pia <pia@example.com>	Fail

Four of the 19 rows were the same person, which is the next step's job.

Rescuing addresses, and the trap in doing it

Some failures are real addresses wrapped in junk: mailto: prefixes, Name <address> formats, two addresses in one cell. REGEXEXTRACT can pull the address out:

=IFERROR(REGEXEXTRACT(A2, "[A-Za-z0-9._%+'-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}"), "")

On our file this correctly recovered finn@example.com, pia@example.com and the first of Ola's two addresses. It also turned hana o@example.com into o@example.com, a confident wrong answer. Never apply extraction to a whole column blind. Run it only on rows that failed Step 2, and read what comes back.

Step 3: Remove duplicates on the normalised column

Dedupe on column B, not on the raw addresses. Then the hidden-character problem from Step 1 cannot leave copies behind.

You have two built-in options:

• Data > Data cleanup > Remove duplicates. Google's help page says "cells with identical values but different letter cases, formatting, or formulas are considered to be duplicates". It deletes rows in place, so work on a copy of the tab.
• =UNIQUE(B2:B) in a new tab. This leaves the original untouched. Google's UNIQUE documentation warns that if apparent duplicates come back, the cause is usually "differing hidden text such as trailing spaces", which is exactly what the normalised column removes.

On our test file, the four variants of Ana's address collapsed to one, leaving 15 distinct values before the syntax filter and 4 that passed it.

Step 4: Make failures visible

Highlight rows that fail rather than deleting them silently. Someone will ask why a contact vanished.

Select your data, open Format > Conditional formatting, choose Custom formula is (documented in Google's conditional formatting guide) and enter =$C2=FALSE. Failed rows turn red. Filter on column C to review them, fix what is fixable by hand, and leave the rest out of the export.

This is also the point to flag role addresses if you treat them differently, with something like =REGEXMATCH(B2, "^(info|sales|admin|support|contact|hello)@"). See whether to send to info@ and sales@ for when that matters.

Step 5: Export, verify, bring the results back

Export only the column that passed, verify it outside Sheets, then join the results back by address. This is where you find out which well-formed addresses are dead.

1. Copy the passing, deduplicated addresses from column B into a new tab with a header row named email.
2. File > Download > Comma-separated values (.csv) for that tab.
3. Upload the file to a verifier. It should check MX records, hold an SMTP conversation with each domain's mail server, and flag catch-all, disposable and role addresses. You can test single addresses first with the free email verifier.
4. Import the results file into a new tab called results. SimpleVerifier's results file has email, status and reason columns; other tools use different names, so check the header row.
5. Back on your main tab, in D2:

=XLOOKUP(B2, results!A:A, results!B:B, "not verified")

XLOOKUP does an exact match by default, and the fourth argument replaces #N/A with a readable label for rows that never went to the verifier, such as the ones that failed syntax.

Now filter column D. Send to valid. Remove invalid. Handle risky deliberately: on a catch-all domain, nothing outside the company can confirm a specific mailbox, which this explainer on catch-all domains covers in detail. Sending those separately and in smaller batches keeps one bad segment from dragging down the rest.

Mistakes we see repeatedly

• Treating ISEMAIL TRUE as "verified". It means the text is shaped correctly. nobody-here-12345@gmail.com passes every formula in this post, and bounces if no such mailbox exists.
• Running Remove duplicates on the raw column. Hidden characters survive it. Normalise first.
• Overwriting the original column. Keep column A as it came in, so you can trace any change.
• Re-using results months later. Mailboxes close continuously. Verification describes the list on the day it ran, so re-check before a send if the list has been sitting.
• Assuming an add-on runs inside Google. It cannot hold an SMTP conversation from Apps Script, so your list is going to a third party. Check their data handling before you upload customer data.

The short version

Sheets handles the text: normalise with LOWER(TRIM(SUBSTITUTE(A2, CHAR(160), " "))), syntax-check with REGEXMATCH, dedupe on the cleaned column, and highlight failures instead of deleting them. Sheets cannot see the network, so the question "does this mailbox exist?" has to go out as a CSV and come back through XLOOKUP. If your list lives in Excel instead, the same process with Excel's functions is in cleaning and verifying an email list in Excel.

To estimate what verifying the cleaned list will cost at your volume, the verification cost calculator does the arithmetic.