How to Clean an Email List Before You Send a Campaign

Every address that bounces costs you reputation with the mailbox providers, and many email services advise keeping the bounce rate low, commonly under about 2%. Most avoidable bounces are not dead mailboxes. They are typing mistakes: john@gmial.com, mary@outlok.com, a trailing space or two addresses stuck together. All of those can be found in a spreadsheet.

Step 1: trim and lowercase

=LOWER(TRIM(A2))

Email addresses are not case-sensitive in practice, and a stray space is the most common reason a valid-looking address is rejected. Also remove invisible characters: see removing special characters.

Step 2: fix the typos you can be sure about

The common mistakes are in the domain part, after the @:

Fix only the domains you recognise:

=SUBSTITUTE(B2,"@gmial.com","@gmail.com")

Do not guess at the name before the @: jon@ might be right. See fixing email typos in Excel for the full method, or paste the column into the free email checker, which runs in your browser and sends nothing anywhere.

Step 3: flag addresses with the wrong shape

=AND(ISNUMBER(FIND("@",B2)),LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))=1,ISNUMBER(FIND(".",B2,FIND("@",B2))),ISERROR(FIND(" ",B2)))

TRUE means exactly one @, a dot after it and no spaces. Look at every FALSE by hand. Some are two addresses typed into one cell, some are phone numbers, some can be repaired.

Step 4: remove duplicates

Compare the lower-case, trimmed column: =COUNTIF($B$2:$B$5000,B2)>1. Keep one row per address, and when the rows differ, keep the one with the newest or fullest data. See finding duplicates and removing them safely.

Step 5: decide about role and suspicious addresses

Addresses such as info@, sales@ or admin@ belong to a team rather than a person. They are valid, but they behave differently, and some businesses prefer not to mail them. Placeholder values such as test@test.com or none@none.com are worth removing. This is a judgment call, so list them and decide.

What a spreadsheet cannot do

A spreadsheet cannot know whether anna@company.com is a working mailbox, whether a company has closed or whether a domain accepts mail. Only sending, or a verification service that contacts the mail server, finds that out. Send your first campaign to a small batch and look at the bounces before sending to everybody.

A whole file at once

DecodingPro fixes mistyped email domains, trims and lowercases addresses, flags invalid shapes and duplicates, and leaves any address it is unsure about untouched and listed for you.

Questions people ask