# Clean a Messy Contact Column in Google Sheets

> A pasted contact column arrives fused, mis-cased and duplicated. Here are the Google Sheets formulas that turn it into clean name and email columns.

- Source: https://www.8apps.co/blog/clean-messy-contact-list-google-sheets
- Markdown: https://www.8apps.co/blog/clean-messy-contact-list-google-sheets.md
- Published: 2026-09-02
- Author: 8apps Team
- Language: en

---
## 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](/google-workspace/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.

## Sources

- [Google Sheets function list](https://support.google.com/docs/table/25273) – read 2026-09-03
- [Remove duplicate data – Google Docs Editors Help](https://support.google.com/docs/answer/9146868) – read 2026-09-03