How to Clean Email Lists in Google Sheets
About the author
Andrew Apell is the creator of Flookup Data Wrangler and has built data cleaning tools for Google Sheets since 2018. The method in this post reflects work on large reconciliation tasks with a government team and continued testing with Sheets users. Every Flookup result described here can be checked in the demo spreadsheet linked from Documentation before purchase.
Read Our Story or contact the team with questions about the steps above.
Key Takeaways
- Email columns collect trailing spaces and mixed case plus display names and domain typos that break sends and inflate list size.
- TRIM and LOWER plus REGEXMATCH format checks fix most rows with built-in formulas before any add-on is needed.
- Fuzzy matching flags domain typos such as gamil versus gmail plus near-duplicate addresses that exact tools miss.
- Flookup prepares the list for sending while mailbox verification stays with a dedicated verifier, so each tool does the task it fits best.
Why Email Columns Go Dirty
Form responses and CRM exports plus event signups and partner lists all land in the same sheet with different habits. One system writes John Smith in angle brackets around the address while another writes the address in capitals with a trailing space. Copy and paste adds non-breaking spaces that look empty yet break matching. Every import adds a few near-duplicates, so a list of 500 signups can hide 40 addresses that will never deliver.
Cleaning means three passes in one sheet. First fix spacing and case. Then check format and split compound cells. Then remove duplicates and flag typos for review.
Back Up Before You Clean
Duplicate the tab or the file before any bulk change. Name the copy with the date so the original stays intact while the working tab takes every edit. Version history helps, yet a named copy is faster to restore when a filter hides rows by mistake.
Trim Spaces and Standardise Case
Extra spaces cause most false mismatches. Wrap the email column with TRIM to strip leading and trailing spaces, then LOWER so Gmail and GMAIL compare as equal. Apply =ARRAYFORMULA(IF(A2:A="", "", LOWER(TRIM(A2:A)))) down the column in one step.
Paste from the web often carries CHAR(160), the non breaking space that TRIM leaves behind. When matches still fail on clean-looking rows, strip it first with =ARRAYFORMULA(IF(A2:A="", "", LOWER(TRIM(SUBSTITUTE(A2:A, CHAR(160), " "))))) before any comparison runs.
Finish by pasting results as values with Ctrl plus Shift plus V so later sorts and imports read plain text rather than formulas.
Split Display Names and Multi-Address Cells
Exports often pack a name and address into one cell, while shared inboxes list two addresses split by commas or line breaks. Each pattern needs its own fix before validation starts.
Pull the address from angle brackets with =REGEXEXTRACT(A2, "<([^<>]+)>") then split the local part from the domain with =SPLIT(B2, "@") for row-level checks.
When one cell holds two addresses, split on commas and line breaks into separate rows so every address gets its own verdict. Keep name and source plus row ID beside each new row so results merge back without guesswork.
Flag Format Problems with REGEXMATCH
A format check catches rows that no verifier can save, such as missing at signs or spaces inside the address. Add a helper column with =REGEXMATCH(C2, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$") and filter every FALSE for manual repair or removal.
If the pattern looks cryptic, the REGEXMATCH help page explains each token with Sheets examples. You can also describe the rule in plain words to Sheets AI Assistant and it returns a formula to test, as shown in the Sheets AI Assistant guide.
Format checks prove shape only. They never confirm that the domain receives mail or that the mailbox exists, which is the task covered later in the verifier section.
Catch Domain Typos with Fuzzy Matching
The typo that costs most sends sits in the domain, such as gamil versus gmail or hotmial versus hotmail. Exact formulas pass these rows because the shape looks valid, so score similarity instead.
Extract the domain with =REGEXEXTRACT(C2, "@(.+)") then run Compare Similarity in Flookup Data Wrangler against a short list of expected domains. Scores above 0.85 flag likely typos for review while lower scores point to rare but valid providers.
Review each flagged pair before editing, since a small provider can sit close to a large one. Correct confirmed typos in the working tab and keep a note of the change for the audit trail. When the extraction needs a custom pattern, the plain words route works again. Ask Sheets AI Assistant for the formula, then check the returned pattern on two or three rows before filling the column.
Deduplicate the List
Duplicates waste sends and skew open rates, so remove exact copies first with Data cleanup plus Remove duplicates. Near-duplicates need a second pass because casing and dots plus tagged suffixes hide repeats from exact tools.
Standardise with LOWER and TRIM, then run Smart Deduplicate in Flookup Data Wrangler to group near-duplicate addresses by confidence. Approve high-confidence groups in the review sheet and inspect the borderline band row by row.
Keep one master row per person with source and consent date beside the address so future imports match against a clean reference.
What Flookup Does and What a Verifier Does
Sheet hygiene and mailbox verification answer different questions. Flookup fixes format plus duplicates plus typos inside the spreadsheet, while a verifier asks the mail server whether the mailbox accepts mail.
Run the sheet pass first, then export the working tab with File plus Download plus Comma-separated values and verify with a dedicated service. For background on server-side checks, see the DeBounce guide to Sheets validation and the VeriMails export and verify workflow, then import verdicts as a new tab and filter by status.
Never treat every non-invalid result as ready to send. Hold catch-all plus disposable plus role-based rows in a review tab and mail the valid segment first.
A Worked Example
A newsletter export holds 500 rows with names in column A and raw emails in column B. The pass runs TRIM plus LOWER in column C with REGEXMATCH flags in column D and domain extraction in column E. 12 rows fail format while 9 domains score above 0.85 against gmail and 31 exact duplicates collapse to 11 masters. The sheet keeps 457 send-ready rows with a full note beside every removed or edited address.
Send-Ready Checklist
| Check | Action | Target |
|---|---|---|
| Backup | Copy tab with date in name | Original untouched |
| Format | REGEXMATCH helper column | Zero FALSE rows left |
| Domains | Fuzzy review above 0.85 | Typos fixed or logged |
| Duplicates | Exact pass then fuzzy pass | One master row per person |
| Verdicts | Verifier import as new tab | Valid segment only mailed |
Clean Lists Send Better
A clean list protects sender reputation and keeps reports honest. Run the sheet pass on every import and re-verify lists that sat unused for 90 days or more.
Frequently Asked Questions
How do I clean email addresses in Google Sheets?
Trim spaces with TRIM and force lowercase with LOWER, then check shape with REGEXMATCH and split display names into plain addresses. Remove exact duplicates with Data cleanup plus Remove duplicates and group near-duplicates with Smart Deduplicate in Flookup Data Wrangler.
Does Google Sheets verify that an email address exists?
No, Google Sheets checks shape only through formulas such as REGEXMATCH. Confirming that a domain receives mail and that a mailbox accepts messages needs a dedicated mailbox verifier after the sheet pass, as described in the DeBounce guide to Sheets validation.
How do I remove duplicate email addresses in Google Sheets?
Remove exact copies first with Data cleanup plus Remove duplicates, then standardise with LOWER and TRIM and run Smart Deduplicate in Flookup Data Wrangler to group near-duplicates such as casing variants and plus-tagged addresses for review.
What causes most email bounces from spreadsheet lists?
Domain typos plus duplicate rows plus stale addresses cause most bounces. Typos such as gamil for gmail pass format checks, duplicates waste sends and old work addresses stop delivering when people change jobs.