How to Clean and Verify an Email List in Excel, Step by Step

Excel can clean an email list, remove duplicates and reject malformed addresses, but it cannot confirm that a mailbox exists. Normalise the column with SUBSTITUTE, TRIM and LOWER, syntax-check it with REGEXTEST (Microsoft 365) or a fallback formula, dedupe the cleaned column, then export it to a verifier and pull the results back with XLOOKUP.

Which formulas you can use depends on your Excel version, and the older-version fallback is weaker than most guides admit. We tested both against the same 19-row messy file to show exactly where.

First, check which Excel you have

The regex functions exist only in Excel for Microsoft 365, so the method splits by version. Microsoft lists REGEXTEST as applying to "Excel for Microsoft 365" on Windows and Mac. UNIQUE reaches further back, to Excel 2021 and 2024.

Your version	Syntax check	Dedupe
Excel for Microsoft 365	REGEXTEST (strict)	UNIQUE or Remove Duplicates
Excel 2021 or 2024	Fallback formula (looser)	UNIQUE or Remove Duplicates
Older versions	Fallback formula (looser)	Remove Duplicates

If a formula below returns #NAME?, your version does not have that function.

Step 1: Normalise every address into a helper column

Make a cleaned copy of each address before you check or dedupe anything. Raw exports carry stray spaces, capitals and invisible characters.

With addresses in column A, put this in B2 and fill down:

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

Each part has a job:

• SUBSTITUTE(A2, CHAR(160), " ") converts non-breaking spaces into ordinary ones. Microsoft's own TRIM documentation explains that TRIM "was designed to trim the 7-bit ASCII space character (value 32)" and "does not remove this nonbreaking space character" (value 160). These arrive whenever addresses are copied from a web page or some CRM screens.
• TRIM then strips leading and trailing spaces.
• LOWER makes Ana.Silva@Example.com and ana.silva@example.com identical, which they are in practice. RFC 5321 technically allows a case-sensitive local part but says exploiting it "impedes interoperability and is discouraged".

If your data came from an old system and contains line breaks or control characters, wrap the inner part in CLEAN. Microsoft's CLEAN page notes it removes ASCII codes 0 to 31 only.

Keep column A untouched. When someone asks why a contact disappeared, you want the original.

Step 2: Syntax-check the cleaned address

On Microsoft 365: REGEXTEST

REGEXTEST returns TRUE when the text matches a pattern, and lets you set the rule precisely. In C2:

=REGEXTEST(B2, "^[a-z0-9_%+'-]+(\.[a-z0-9_%+'-]+)*@[a-z0-9]([a-z0-9-]*[a-z0-9])?(\.[a-z0-9]([a-z0-9-]*[a-z0-9])?)*\.[a-z]{2,}$")

Excel's regex functions use the PCRE2 flavour and are case-sensitive by default, which is why the pattern runs on the lower-cased column B. The pattern rejects spaces, double @, consecutive dots, a dot at either end of the local part, hyphens at the edge of a domain label, and a missing or one-letter top-level domain. It accepts apostrophes and plus signs, which real addresses use.

On older Excel: a fallback formula

Without regex you can still catch the worst errors, but some malformed addresses will pass. In C2:

=AND(B2<>"", ISERROR(FIND(" ",B2)), LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))=1, LEFT(B2,1)<>"@", RIGHT(B2,1)<>".", ISERROR(FIND("..",B2)), ISNUMBER(FIND(".",B2,FIND("@",B2)+2)))

In plain terms: not empty, no spaces, exactly one @, does not start with @ or end with ., no double dots, and at least one dot in the domain after the first character.

How the two compared on the same file

We ran both formulas over a 19-row test file on 1 October 2026. Our attempt to drive the installed Excel by script timed out, so we re-implemented each function's documented behaviour in Python and ran that instead; the script, input file and output are in our research notes.

Cleaned value	REGEXTEST pattern	Fallback formula
ana.silva@example.com	Pass	Pass
o'brien@example.co.uk	Pass	Pass
jo+news@example.com	Pass	Pass
kai@sub.example.io	Pass	Pass
ben@example	Fail	Fail
cara@@example.com	Fail	Fail
dev@example..com	Fail	Fail
eve@example,com	Fail	Fail
hana o@example.com	Fail	Fail
mailto:finn@example.com	Fail	Pass
gus.@example.com	Fail	Pass
max@-example.com	Fail	Pass
ned@example.c	Fail	Pass

The fallback let four malformed values through. None of them will be delivered, so on older Excel the verification step later on is doing more of the work, and you should expect it to catch some addresses your formula approved.

Step 3: Remove duplicates on the cleaned column

Always dedupe on column B, never on the raw column A. That way, capitals and hidden spaces cannot leave copies behind.

Data > Data Tools > Remove Duplicates deletes rows in place. Microsoft's guide to removing duplicates makes three points worth knowing:

• The comparison "depends on what appears in the cell", not the underlying value.
• "The first occurrence of the value in the list is kept."
• Removing duplicates is permanent, and Microsoft suggests filtering for unique values first to check the result.

Select the whole table, not just column B, and tick only column B in the dialog. Otherwise you will dedupe the email column and leave the rest of each row misaligned.

On Microsoft 365, 2021 or 2024, =UNIQUE(B2:B5000) in a new sheet gives you the distinct list without touching the original.

On our test file, the four variants of one address (different capitals, a trailing space, a non-breaking space) collapsed into one, leaving 15 distinct non-empty values.

Step 4: Review the failures, then fix or drop them

Filter column C to FALSE and read every row before you delete anything. Many failures are real addresses with junk around them.

Common patterns and what to do:

• mailto: prefix, Name <address> formats, two addresses separated by ;. Fix by hand, or with Find and Replace for the mailto: case.
• Comma instead of a dot (example,com). A typo; fix it if the intended address is obvious.
• A space inside the address. Do not just delete the space. hana o@example.com could be hanao@ or hana.o@, and guessing creates an address you never had permission to email.
• No top-level domain (ben@example). Usually a truncated import. Go back to the source.

If you flag role addresses separately, a simple check on Microsoft 365 is =REGEXTEST(B2, "^(info|sales|admin|support|contact)@"). Whether to send to them is a judgement covered in should you send to info@ and sales@.

Step 5: Export, verify, and bring the results back

Excel's work ends at "correctly formed". Whether the mailbox exists is a network question. It needs an MX lookup on the domain and an SMTP conversation with its mail server, described in checking an address without sending.

1. Copy the passing, deduplicated values from column B into a new sheet with the header email.
2. File > Save As, and pick a CSV format. If your version offers a UTF-8 CSV option, use it, so apostrophes and accented characters survive.
3. Upload the CSV to a verifier. Try a few addresses in the free email verifier first if you want to see the result format.
4. Open the results file and copy it into a sheet named results. SimpleVerifier returns email, status and reason columns; check the header row of whichever tool you use.
5. Back on the main sheet, in D2:

=XLOOKUP(B2, results!A:A, results!B:B, "not verified")

The fourth argument of XLOOKUP is the text returned when there is no match, so rows that failed syntax read "not verified" instead of #N/A. If your Excel has no XLOOKUP, use =IFERROR(INDEX(results!B:B, MATCH(B2, results!A:A, 0)), "not verified").

Filter column D:

• valid: send.
• invalid: remove. These would have been hard bounces.
• risky: decide deliberately. Catch-all domains accept every address, so no external check can confirm a specific mailbox; what a catch-all domain is explains why. Send these separately, in smaller batches, so they cannot drag down the rest of the list.

Common mistakes

• Treating a TRUE syntax check as verified. nobody-here-12345@gmail.com passes every formula in this post and bounces if no such mailbox exists.
• Sorting one column on its own after deleting rows. The emails and names drift apart. Always select the full table.
• Running Remove Duplicates before normalising. Hidden characters survive and you end up emailing people twice.
• Saving over the original file. Keep the raw export; work in a copy.
• Reusing old verification results. Results describe the list on the day they ran. A list that has been sitting for months needs checking again.

The takeaway

In Excel, normalise with LOWER(TRIM(SUBSTITUTE(A2, CHAR(160), " "))), syntax-check with REGEXTEST if you have Microsoft 365 or the fallback formula if not, dedupe on the cleaned column, and read the failures before dropping them. Then send the survivors out for real verification and join the results back with XLOOKUP. If you would rather script it, removing invalid and duplicate emails from a CSV does the same steps in a short Python file, and the Google Sheets version is in verifying emails in Google Sheets.