Spreadsheet Data Cleaning Techniques for Email Lists

When to Clean in a Spreadsheet

Spreadsheets are the right tool when your list is small enough to manage visually (under 100,000 rows), when you need to review individual records, or when you do not have access to specialised tools or scripts.

For larger lists, or when you need to extract emails from files in formats like PDF, DOCX or JSON, Email Extractor processes up to 25 MB per file and handles extraction and deduplication automatically. After extracting, you can download the results as CSV and do further cleaning in your spreadsheet.

This guide covers techniques in both Excel and Google Sheets. Most formulas work in both, with differences noted.

Trimming Whitespace

Extra spaces cause duplicate detection failures, CRM import errors and broken personalisation tokens.

TRIM function

Removes leading, trailing and extra internal spaces (collapses multiple spaces to one).

=TRIM(A2)

CLEAN function

Removes non-printable characters (tabs, line breaks, control characters) that can hide in pasted data.

=CLEAN(TRIM(A2))

Combined cleanup

=SUBSTITUTE(CLEAN(TRIM(A2)),CHAR(160)," ")

This handles regular spaces, non-breaking spaces (CHAR 160, common in web-copied data) and non-printable characters.

Email-Specific Cleaning

Convert to lowercase

Email addresses are case-insensitive. Standardise to lowercase for consistent matching.

=LOWER(A2)

Validate email syntax

Check if a value looks like a valid email address:

Excel:

=AND(
  ISERROR(FIND(" ",A2)),
  LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1,
  FIND("@",A2)>1,
  FIND(".",A2,FIND("@",A2))>FIND("@",A2)+1,
  LEN(A2)-FIND(".",A2,FIND("@",A2))>=2
)

This checks: no spaces, exactly one @ sign, something before the @, a dot after the @, and at least two characters after the last dot.

Google Sheets (REGEXMATCH):

=REGEXMATCH(A2,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")

This uses a regex pattern for more accurate validation.

Flag common domain typos

Check for known misspellings:

=IF(OR(
  RIGHT(A2,9)="@gmal.com",
  RIGHT(A2,10)="@gnail.com",
  RIGHT(A2,10)="@gmial.com",
  RIGHT(A2,9)="@yaho.com",
  RIGHT(A2,11)="@hotmal.com",
  RIGHT(A2,11)="@outlok.com"
), "CHECK DOMAIN", "OK")

Extract domain from email

Useful for analysis (which domains are most common) and for identifying role-based or disposable addresses.

=RIGHT(A2, LEN(A2)-FIND("@",A2))

Flag role-based addresses

Role-based addresses (info@, admin@, support@, sales@) often have lower deliverability and engagement.

Excel:

=IF(OR(
  LEFT(A2,5)="info@",
  LEFT(A2,6)="admin@",
  LEFT(A2,8)="support@",
  LEFT(A2,6)="sales@",
  LEFT(A2,11)="webmaster@",
  LEFT(A2,11)="postmaster@",
  LEFT(A2,6)="abuse@",
  LEFT(A2,4)="noc@"
), "Role-based", "Personal")

Google Sheets:

=IF(REGEXMATCH(A2,"^(info|admin|support|sales|webmaster|postmaster|abuse|noc|contact|help|billing|office|team|hello)@"), "Role-based", "Personal")

See Role-Based Email Addresses.

Flag disposable email domains

Disposable email services indicate temporary or fake signups.

Create a reference list of known disposable domains on a separate sheet (Sheet2, column A):

=IF(ISNUMBER(MATCH(RIGHT(A2,LEN(A2)-FIND("@",A2)), Sheet2!$A:$A, 0)), "Disposable", "OK")

Common disposable domains include: mailinator.com, guerrillamail.com, tempmail.com, throwaway.email, yopmail.com and hundreds of others.

See Disposable Email Addresses.

Removing Duplicates

Excel: built-in tool

1. Select your data range.
2. Data tab, Remove Duplicates.
3. Select which columns to check (typically the email column only).

Limitation: This permanently deletes rows. Make a backup first.

Google Sheets: built-in tool

1. Select your data range.
2. Data menu, Data cleanup, Remove duplicates.
3. Select columns to check.

Formula-based duplicate flagging

Instead of deleting, flag duplicates so you can review them first.

=IF(COUNTIF($A$2:A2, A2)>1, "DUPLICATE", "UNIQUE")

This marks the second (and subsequent) occurrence of each email as "DUPLICATE" while keeping the first occurrence as "UNIQUE."

Case-insensitive duplicate detection

The COUNTIF approach above is case-insensitive in Excel but case-sensitive in Google Sheets. In Google Sheets, compare lowercase versions:

=IF(COUNTIF(ARRAYFORMULA(LOWER($A$2:A2)), LOWER(A2))>1, "DUPLICATE", "UNIQUE")

Name Cleaning

Proper case (title case)

Standardise names to title case:

=PROPER(A2)

Limitation: PROPER capitalises after every space, apostrophe and hyphen. This handles "John Doe" and "Mary-Jane" correctly but may miscapitalise "McDonald" (becomes "Mcdonald") or "van der Berg" (becomes "Van Der Berg").

Fix PROPER case exceptions

For names with known patterns, apply corrections after PROPER:

=SUBSTITUTE(SUBSTITUTE(PROPER(A2),"Mc","Mc"),"Mac","Mac")

For more complex cases, manual review or a script is more reliable.

Split full name into first and last

First name (everything before the first space):

=LEFT(A2, FIND(" ",A2)-1)

Last name (everything after the first space):

=MID(A2, FIND(" ",A2)+1, LEN(A2))

Handling names with no space:

=IFERROR(LEFT(A2, FIND(" ",A2)-1), A2)

This returns the full value if there is no space (single-word name).

Handle "Last, First" format

=TRIM(MID(A2, FIND(",",A2)+1, LEN(A2))) & " " & LEFT(A2, FIND(",",A2)-1)

This converts "Smith, John" to "John Smith."

Conditional Formatting for Visual Review

Highlight invalid emails

Apply conditional formatting to flag emails that fail syntax validation:

Excel:

1. Select the email column.
2. Home, Conditional Formatting, New Rule.
3. Use a formula: =NOT(AND(ISERROR(FIND(" ",A2)),LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1))
4. Set format to red fill.

Google Sheets:

1. Select the email column.
2. Format, Conditional formatting.
3. Custom formula: =NOT(REGEXMATCH(A2,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"))
4. Set format to red fill.

Highlight duplicates

Excel:

1. Select the email column.
2. Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values.

Google Sheets:

1. Select the email column.
2. Format, Conditional formatting.
3. Custom formula: =COUNTIF($A:$A, A1)>1

Highlight blanks

Flag rows with missing email addresses:

Conditional formatting formula: =ISBLANK(A2) or =LEN(TRIM(A2))=0

Data Validation

Prevent bad data entry

Set up data validation rules on email input columns to prevent problems at the source.

Excel:

1. Select the email column.
2. Data, Data Validation.
3. Allow: Custom.
4. Formula: =AND(ISERROR(FIND(" ",A2)),LEN(A2)-LEN(SUBSTITUTE(A2,"@",""))=1)

Google Sheets:

1. Select the email column.
2. Data, Data validation.
3. Custom formula: =REGEXMATCH(A2,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")

Working with Large Lists

Performance tips

Avoid volatile functions. INDIRECT, OFFSET, NOW, TODAY recalculate on every change. In large sheets, this causes lag.

Use helper columns. Instead of nested formulas, break complex operations into separate columns. This is easier to debug and faster to calculate.

Convert formulas to values. After cleaning, copy the cleaned column and paste as values. This removes the formula overhead and locks in the results.

Filter before processing. Use AutoFilter to work with subsets of data. Filter to show only duplicates, only invalid emails, or only a specific domain.

Batch processing

For lists too large for comfortable spreadsheet work:

1. Split the file into chunks (10,000-20,000 rows each).
2. Clean each chunk.
3. Recombine and deduplicate across chunks.

Or use Email Extractor for the extraction and deduplication step. Upload your spreadsheet (XLSX, XLS, ODS, CSV), extract all email addresses, and download the deduplicated results. Then do any remaining cleaning (name formatting, domain analysis) in your spreadsheet.

Common Cleaning Workflow

1. Import. Open your raw data in Excel or Google Sheets.
2. Backup. Duplicate the sheet before making changes.
3. Trim and clean. Apply CLEAN(TRIM()) to all text columns.
4. Lowercase emails. Apply LOWER() to the email column.
5. Validate syntax. Add a validation column. Flag invalid emails.
6. Flag domains. Check for typos, role-based addresses and disposable domains.
7. Remove duplicates. Flag first, review, then remove.
8. Clean names. Apply PROPER(), split first/last, fix exceptions.
9. Convert to values. Copy cleaned columns, paste as values.
10. Export. Save as CSV for CRM import or further processing.

Google Sheets-Specific Features

REGEXREPLACE

Replace patterns in text:

=REGEXREPLACE(A2, "\s+", " ")

This collapses multiple spaces into one.

REGEXEXTRACT

Extract the email address from a cell that contains other text:

=REGEXEXTRACT(A2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")

UNIQUE

Return unique values from a range:

=UNIQUE(A2:A1000)

This outputs a deduplicated list without modifying the original data.

FILTER

Return rows matching a condition:

=FILTER(A2:D1000, B2:B1000="Valid")

This outputs only rows where column B contains "Valid."

Excel-Specific Features

Flash Fill

Excel's Flash Fill recognises patterns and fills a column based on examples.

1. In a new column next to your data, type the cleaned version of the first value.
2. Start typing the second value. Excel suggests the pattern.
3. Press Enter to accept.

Useful for standardising formats, extracting parts of text and reformatting data.

Power Query

For repeatable cleaning workflows:

1. Data tab, Get Data, From Table/Range.
2. Apply transformations (trim, lowercase, remove duplicates, filter).
3. Close and Load.

Power Query transformations are recorded and can be reapplied when the source data changes.

Related Guides

• How to Clean an Email List
• Normalizing Email Formats
• Removing Duplicates at Scale
• Data Quality Metrics for Email Lists
• Automating Data Cleaning Workflows