clean-messy-contact-list-google-sheets

8apps Team

title: "Clean a Messy Contact Column in Google Sheets" seoTitle: "Clean a Messy Contact Column in Google Sheets" description: ""Jane Doe jane@x.com", trailing spaces, duplicates in three cases – here are the formulas that turn a pasted contact mess into clean name and email columns." date: "2026-09-02" categories: ["Google Workspace", "Productivity"] tags: ["google sheets", "data cleanup", "formulas", "duplicates", "contact list", "contact extractor", "8apps"] author: "8apps Team"

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 reference (SPLIT, REGEXEXTRACT, REGEXREPLACE, TRIM, PROPER, LOWER, COUNTIF, UNIQUE/TOCOL) – cite the specific support.google.com function pages at publish time
← Back to Blog