Cleaning an email list in a spreadsheet takes four passes: lowercase and trim every address, flag structurally invalid ones (no @, spaces, doubled dots), remove duplicates, and only then worry about deliverability, which a spreadsheet cannot test. Here is each pass with the exact formulas, and what honestly requires an email verification service.
Pass 1: normalize
Email addresses are case-insensitive in practice, so [email protected], [email protected], and [email protected] are the same inbox and should be one row. In Excel: =LOWER(TRIM(A2)), fill down, paste back as values. This single pass typically exposes most of the duplicates in a hand-collected list.
Pass 2: flag the structurally broken
A spreadsheet can verify structure, not existence. These formulas catch the common breakage:
| Problem | Formula to flag it |
|---|---|
| Missing @ | =ISERROR(FIND(“@”,A2)) |
| Spaces inside | =ISNUMBER(FIND(” “,TRIM(A2))) |
| No dot after the @ | =ISERROR(FIND(“.”,MID(A2,FIND(“@”,A2),100))) |
| Doubled dots | =ISNUMBER(FIND(“..”,A2)) |
Combine them into one audit column with OR(), filter for TRUE, and you have a worklist. Fix the obvious typos (gamil.com, .con) by hand; delete the hopeless ones.
Pass 3: remove duplicates
After normalization, Data > Remove Duplicates on the email column alone. If you skipped Pass 1, this step silently fails on case and whitespace variants; the duplicates guide covers why.
Pass 4: what a spreadsheet cannot do
No formula can tell you whether [email protected] still works, whether the domain accepts mail, or whether the address is a spam trap. That requires an SMTP-level verification service, and if you send to a large cold list without one, bounce rates will hurt your sender reputation. The honest division of labor: spreadsheet (or our free tool) for structure and consistency, a verification service for deliverability, and consent practices for everything else.
The Gmail nuances worth knowing
- Gmail ignores dots in the local part: [email protected] and [email protected] deliver to the same inbox. Most lists treat them as distinct, which inflates counts.
- Plus-aliases ([email protected]) are the same inbox with a tag. Whether to collapse them depends on whether you are counting people or signups.
- Neither rule applies universally outside Gmail, so collapsing these everywhere is wrong; do it knowingly or not at all.
Doing the mechanical passes in one step
Our data cleaning tool handles Pass 1 automatically: it detects email columns, lowercases and trims every address, and leaves anything structurally suspicious unchanged rather than guessing, with every change shown in a before/after preview. Free for CSV files up to 2MB or 2,500 rows; the rules are documented in How Cleaning Works.
FAQ
Is there a formula that fully validates an email address?
No. Even the official format specification (RFC 5322) permits addresses most providers reject, and a perfectly formatted address can still be dead. Structure checks catch typos; only SMTP verification confirms a live inbox.
Should I delete role addresses like info@ and sales@?
For marketing lists, usually yes: they bounce and complain at higher rates and often violate list-quality policies of email providers. For operational contact lists, keep them.
What bounce rate is considered a problem?
Mailbox providers and ESPs commonly treat sustained bounce rates above roughly 2% as a sender-reputation warning sign. If a list has sat unused for a year, verify before sending.
Leave a Reply