How to Remove Invalid and Duplicate Emails From a CSV File

To remove invalid and duplicate emails from a CSV, lower-case and trim the email column, deduplicate on that cleaned value, drop addresses that fail a syntax check, then drop domains with no mail server. Excel and Google Sheets can do the first three; a short Python script does all four and logs why each row was removed.

Syntax and MX checks catch formatting errors and dead domains. They cannot tell you whether a mailbox exists, which needs an SMTP conversation with the recipient's server. Treat this as the free first pass before real verification.

What each method can do

Task	Excel	Google Sheets	Python script below
Lower-case and trim	Yes	Yes	Yes
Non-breaking spaces	Needs SUBSTITUTE(…, CHAR(160), " ")	Needs SUBSTITUTE too	Handled
Remove duplicates	Yes	Yes	Yes, keeps first
Syntax check	Regex only on Microsoft 365	Yes, REGEXMATCH	Yes
Log why each row was removed	Manual	Manual	Automatic
MX check	No	No	Yes, with dnspython
Mailbox exists	No	No	No

For files of a few thousand rows that you clean once, a spreadsheet is fine. For repeated cleaning, very large files, or anything you need an audit trail for, use the script.

In Excel or Google Sheets

Normalise in a helper column, syntax-check that column, then dedupe on it. We have full, tested walkthroughs for Excel and Google Sheets, so here is the short version.

1. Helper column: =LOWER(TRIM(SUBSTITUTE(A2, CHAR(160), " "))). The SUBSTITUTE is there because both Excel's TRIM and Sheets' Trim whitespace leave non-breaking spaces in place.
2. Syntax: REGEXMATCH in Sheets, REGEXTEST in Excel for Microsoft 365, with the pattern used in the script below. Older Excel needs a longer fallback formula that, in our test, let four malformed addresses through.
3. Dedupe on the helper column with Remove duplicates (Microsoft documents that Excel keeps the first occurrence), or with UNIQUE into a new sheet.
4. Filter out the rows that failed, then save as CSV.

In Python: a tested 20-line script

This script cleans, checks and deduplicates in one pass, and writes the rejects to a separate file with a reason. It uses only Python's standard library.

import csv, re, sys

PATTERN = re.compile(r"^[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,}$")

src, column = sys.argv[1], sys.argv[2] if len(sys.argv) > 2 else "email"
seen = set()
with open(src, newline="", encoding="utf-8-sig") as f, \
     open("clean.csv", "w", newline="", encoding="utf-8") as ok, \
     open("rejected.csv", "w", newline="", encoding="utf-8") as bad:
    reader = csv.DictReader(f)
    good = csv.DictWriter(ok, fieldnames=reader.fieldnames)
    rejected = csv.DictWriter(bad, fieldnames=reader.fieldnames + ["reason"])
    good.writeheader(); rejected.writeheader()
    for row in reader:
        email = (row.get(column) or "").replace("\u00a0", " ").strip().lower()
        reason = "empty" if not email else "bad syntax" if not PATTERN.match(email) else "duplicate" if email in seen else ""
        if reason:
            rejected.writerow({**row, "reason": reason})
            continue
        seen.add(email)
        good.writerow({**row, column: email})
print(f"kept {len(seen)} unique addresses")

Run it with the file name, and the column name if it is not email:

python3 clean_emails.py leads.csv
python3 clean_emails.py leads.csv "Email Address"

It writes clean.csv (every original column, with the email normalised) and rejected.csv (the original rows plus a reason column: empty, bad syntax or duplicate).

What happened when we ran it

We ran the script on 1 October 2026 (Python 3.14, macOS) against a 19-row test file built from the mistakes that turn up in real exports. It printed kept 4 unique addresses.

Input	Result
ana.silva@example.com	Kept (first occurrence)
Ana.Silva@Example.com	Rejected: duplicate
ana.silva@example.com + trailing space	Rejected: duplicate
ana.silva@example.com + non-breaking space	Rejected: duplicate
o'brien@example.co.uk	Kept
jo+news@example.com	Kept
kai@sub.example.io	Kept
ben@example	Rejected: bad syntax
cara@@example.com	Rejected: bad syntax
dev@example..com	Rejected: bad syntax
eve@example,com	Rejected: bad syntax
gus.@example.com	Rejected: bad syntax
max@-example.com	Rejected: bad syntax
ned@example.c	Rejected: bad syntax
mailto:finn@example.com	Rejected: bad syntax
Pia <pia@example.com>	Rejected: bad syntax
ola@example.com;ola2@example.com	Rejected: bad syntax
hana o@example.com	Rejected: bad syntax
(blank)	Rejected: empty

The last four rejected rows contain real addresses wrapped in junk. That is why the script writes rejects to a file rather than discarding them: open rejected.csv, fix what is obviously fixable by hand, and add those rows back. Do not "fix" ambiguous ones. hana o@example.com could be hanao@ or hana.o@, and guessing creates an address nobody gave you.

Two CSV traps the script avoids

Splitting on commas. The eve@example,com row is stored in the file as Eve,"eve@example,com", quoted because it contains a comma. We checked: a naive line.split(",") turns it into three fields, ['Eve', '"eve@example', 'com"'], while Python's csv module correctly returns two. Always use a real CSV parser.

The byte order mark. Some programs write three invisible bytes, a byte order mark, at the start of UTF-8 files. Opened as plain utf-8, Python reads the first header as '\ufeffemail', so a lookup for email finds nothing and every row looks empty. We reproduced this with a two-line test file. encoding="utf-8-sig" strips the mark if it is there and does nothing if it is not.

A note on the non-breaking space: Python's strip() already treats it as whitespace, unlike the spreadsheet trim functions. The explicit replace also catches one sitting in the middle of an address, which then correctly fails the syntax check.

Adding an MX check

A domain check removes addresses at domains that cannot receive mail, and it is cheap because each domain is looked up once. It needs one package: pip install dnspython.

import csv, sys
import dns.resolver  # pip install dnspython

cache = {}

def mail_status(domain):
    if domain in cache:
        return cache[domain]
    try:
        answers = dns.resolver.resolve(domain, "MX", lifetime=5)
        hosts = [str(r.exchange) for r in answers]
        status = "null MX (accepts no mail)" if hosts == ["."] else "has MX"
    except dns.resolver.NXDOMAIN:
        status = "domain does not exist"
    except dns.resolver.NoAnswer:
        try:
            dns.resolver.resolve(domain, "A", lifetime=5)
            status = "no MX, A record fallback"
        except Exception:
            status = "no mail server"
    except Exception:
        status = "lookup failed, retry"
    cache[domain] = status
    return status

with open(sys.argv[1], newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        email = row["email"]
        print(f"{email:45} {mail_status(email.rsplit('@', 1)[1])}")

Run on a five-row test file, with dnspython 2.8.0 on 1 October 2026, it returned:

someone@gmail.com                             has MX
someone@example.com                           null MX (accepts no mail)
someone@nonexistent-domain-zz91x.com          domain does not exist
someone@neverssl.com                          no MX, A record fallback
other@gmail.com                               has MX

We cross-checked each with dig. Two of these rows are why the script is longer than "does it have an MX record?":

• Null MX. example.com publishes an MX record, but it is 0 ., which RFC 7505 defines as the signal that a domain accepts no mail. A check that only asks "is there an MX record?" passes it.
• A record fallback. neverssl.com has no MX record but does have an A record. RFC 7505's abstract describes mail delivery as "first by looking for an MX record and then by looking for an A/AAAA record as a fallback", so no MX alone does not prove a domain is undeliverable. Flag these for verification rather than deleting them.

Remove domain does not exist, null MX and no mail server. Re-run lookup failed, retry later, because a DNS timeout says nothing about the domain. The MX lookup tool does single domains in a browser if you want to spot-check.

What this does not do

Neither the script nor a spreadsheet can tell you whether a mailbox exists. nobody-here-12345@gmail.com passes the syntax check and the MX check, and bounces if no such mailbox exists. Confirming a mailbox needs a conversation with the recipient's mail server on port 25, which most home and cloud networks block; why cloud providers block port 25 has our measurements. It also cannot see catch-all domains, which accept every address.

Run the cleaned file through a verifier for that step. Because you have already removed duplicates and dead domains, you are paying to check fewer rows.

The takeaway

Normalise before you deduplicate, use a real CSV parser, open files with utf-8-sig, and keep rejected rows with a reason instead of deleting them. Add an MX check that understands null MX and A-record fallback. That clears the formatting errors and dead domains for free; mailbox-level checks are the only remaining step, and the verification cost calculator shows what that costs on what is left.