Every pasted contact list looks like this
Jane Doe <jane.doe@example.com>
SMITH, BOB <BOB@EXAMPLE.COM>
jane.doe@example.com
info@example.com; Priya K <priya@example.co.uk>
Names and addresses fused, casing random, separators inconsistent, duplicates in disguise. Here is the cleanup, formula by formula – and at the end, how to avoid creating the mess at all.
1. One entry per row first
If cells hold several contacts separated by ; or ,, split before anything else:
=SPLIT(A2, ";")
Then stack the results into one column (TOCOL in current Sheets, or paste-special the ranges under each other).
2. Pull the email address out
=REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")
This extracts the first address from any string – fused name-and-email, angle brackets, surrounding text. Wrap in IFERROR(..., "") for rows with no address.
3. Pull the name out
For the common Name <email> shape:
=TRIM(REGEXREPLACE(A2, "<[^>]*>", ""))
– strip the bracketed address, trim what remains. Fix SHOUTING with =PROPER(), and split into first/last with =SPLIT(B2, " "). "SMITH, BOB" needs its own pass: split on the comma and swap the halves.
4. Normalise the email column
=LOWER(TRIM(C2))
Lowercase because Jane@ and jane@ are the same mailbox but different strings to every tool downstream. One warning from hard experience: TRIM() does not remove a non-breaking space – the invisible character CSV exports love. If a "clean" cell still misbehaves, =SUBSTITUTE(C2, CHAR(160), "") first.
5. Dedupe – visibly, then destructively
See the duplicates before deleting them:
=COUNTIF(C:C, C2) > 1
Then Data → Data cleanup → Remove duplicates on the email column only – names differ ("Bob" vs "Robert") on rows that are the same person. Keep the row with the fuller name.
6. The last pass is human
Formulas cannot tell you that info@example.com is a mailbox, not a person, or that a CC bystander does not belong on your outreach list. Sort by domain, scan once, delete what a human would delete.
Or: skip making the mess
If the messy column came out of your own mailbox – pasted from threads, forwarded intros, half-remembered exports – the cleanup is optional. Contact Extractor pulls addresses and names from Gmail already separated, deduplicated and consistently cased, scoped by any search or label. Steps 1–5 are what it does on the way out; step 6 stays yours.
→ Contact Extractor on the Google Workspace Marketplace
Sources
- Google Sheets function list – read 2026-09-03
- Remove duplicate data – Google Docs Editors Help – read 2026-09-03