How to Find and Fix Invalid Email Addresses in Excel

To find invalid email addresses in Excel, put a check formula beside the column and filter for FALSE. In Microsoft 365, =REGEXTEST(TRIM(B2),"^[^@\s]+@[^@\s.]+(\.[^@\s.]+)+$") returns FALSE for an address with no @ or more than one, a space inside it, or a domain with no dot or a double dot. A format check can’t see typos, though: an address at gmial.com passes it. For those, keep a short list of misspelled domains and let XLOOKUP suggest the right one, and use COUNTIF to find the same address entered twice.

Bad addresses bounce when you send to the list, and in a CRM they sit on contact records nobody can reach. Below, the three checks are built on a ten-row list, then joined into one Status column you can filter on. REGEXTEST needs Microsoft 365; TEXTBEFORE and TEXTAFTER need Microsoft 365 or Excel 2024; XLOOKUP and FILTER work in Excel 2021 and later. Versions for Excel 2021 and older are under Common Questions.

The sample list

	A	B
1	Name	Email
2	Ana Ruiz	ana.ruiz@example.com
3	Ben Carter	ben.carter@gmial.com
4	Chloe Park	chloe.park@example..com
5	Dev Patel	dev.patel@examplecom
6	Eva Novak	eva.novak@example.com; eva@example.org
7	Finn Walsh	finn.walsh@hotmial.com
8	Grace Lee	grace lee@example.com
9	Ivy Chen	ivy.chen@outlook.con
10	Kai Moreno	kai.moreno@example.com
11	Ana Ruiz	ANA.RUIZ@example.com

Step 1: Check the format

In C2, filled down to C11:

Read the pattern left to right: some characters that are not an @ or a space, one @, a domain name, then one or more parts made of a dot and a name. TRIM forgives stray spaces at either end, which pasted lists often carry.

If Excel shows #NAME?, your version doesn’t have REGEXTEST yet. This formula catches the same four faults in the sample, in any version:

On the sample, C is FALSE for rows 4, 5, 6 and 8: the double dot, the missing dot, two addresses in one cell, and the space.

Step 2: Catch typos in the big domains

A format check passes gmial.com, because it is a well-formed domain. It just isn’t the one your contact meant. Put the misspellings and their fixes in H1:I9:

	H	I
1	Typo	Fix
2	gmial.com	gmail.com
3	gmai.com	gmail.com
4	gamil.com	gmail.com
5	gmail.con	gmail.com
6	hotmial.com	hotmail.com
7	yahoo.co	yahoo.com
8	outlook.con	outlook.com
9	iclod.com	icloud.com

In D2, filled down:

TEXTAFTER takes the domain, and XLOOKUP finds it in the list and returns the right spelling. A domain that isn’t on the list gives an error, which IFERROR turns into a blank. XLOOKUP ignores case, so GMIAL.COM is caught too. On the sample, D suggests gmail.com for row 3, hotmail.com for row 7 and outlook.com for row 9. Step 4 puts each name back in front of its fixed domain.

Step 3: Find repeats

In E2, filled down: =COUNTIF($B$2:$B$11,B2). Anything above 1 is in the list more than once. COUNTIF ignores case, so ana.ruiz@example.com and ANA.RUIZ@example.com count as the same address, and both rows show 2. It does not trim, though: the same address with a space at the end counts as a different one. If your list has stray spaces, use =SUMPRODUCT(--(TRIM($B$2:$B$11)=TRIM(B2))) instead. It trims both sides, also ignores case, and works in any version. To decide which row to keep, see how to find and remove duplicates based on multiple columns.

Step 4: One status to filter on

In F2, filled down:

Turn on a filter (Data, Filter) and untick OK under Status. Or, in Excel 2021 or later, list the problem rows on their own with =FILTER(A2:B11,F2:F11<>"OK","No problems found"). On this list that is nine rows: 4 Invalid, 3 Typo and 2 Repeat. Only Kai Moreno’s address needs nothing.

To take the suggestions, put =IF(D2<>"",LEFT(TRIM(B2),FIND("@",TRIM(B2)))&D2,TRIM(B2)) in a spare column. It keeps everything up to and including the @, adds the fixed domain, and works in any version. Read the column, then copy it and use Paste Special, Values over column B. The Invalid rows still need fixing by hand, and each repeat needs one row deleted. More on TRIM, LOWER and pasting values over the originals in how to clean messy imported data.

Where formulas stop

The checks above find broken addresses and the typos you thought of. On a real list, they miss more:

• Typos you didn’t list. The lookup knows only the misspellings typed into H:I.
• Placeholders. A test address at test.com has one @ and a dot, so it passes every check.
• Throwaway inboxes. A disposable domain looks like any other domain, so a formula cannot tell one apart without a list of them you build yourself.
• Two addresses in one cell are marked Invalid, but not split. The split is under Common Questions.
• The fixing. Every suggestion still has to be read, accepted and pasted back.

And no check in Excel can tell you whether a mailbox exists. Only sending to it, or a verification service, can.

Doing it in Excel Data Cleaner

In Excel Data Cleaner, emails are one step. Load the list, tag the email column, and run the Emails step. Every address gets a status, with a suggested fix where there is one. In the review you see the original and cleaned values side by side, and for each row you choose Keep Suggestion, Keep Original or Delete Record. Nothing is changed permanently until you approve. Approve, skip the steps the list doesn’t need, and Finalize builds the cleaned list.

Watch it done, 2:37. Twenty addresses from a contact list checked in Excel Data Cleaner: the typos fixed, the fake entries deleted, and the list finalized.

Common questions

Can Excel tell whether an email address really exists?

No. A formula checks the shape of an address, not the mailbox behind it, so a well-formed address passes whether or not anyone receives mail there. Excel Data Cleaner works the same way: it runs on your own computer, and your data never leaves your machine, so it judges the address, the domain’s spelling, placeholders and throwaway domains, not the mailbox. To confirm a mailbox, send to it or use a verification service.

How do I split two email addresses in one cell?

In Microsoft 365 or Excel 2024, =TRIM(TEXTBEFORE(B6,";",,,,B6)) returns the first address, or the whole cell when there is only one, and =TRIM(TEXTAFTER(B6,";",,,,"")) returns the second, or a blank. In any version, Data, Text to Columns, Delimited, with Semicolon ticked, splits the column in place. It leaves the space after the semicolon in front of the second address, so run TRIM on that column afterward. Work on a copy, because Text to Columns overwrites the columns to its right. If the addresses are separated by commas, use a comma instead.

Will these formulas work in Excel 2021 or older?

COUNTIF, SUMPRODUCT, the Status formula and the formula that takes the suggestions work in any version, and the AND formula in Step 1 stands in for REGEXTEST. Excel 2021 has XLOOKUP but not TEXTAFTER, so for the typo lookup use VLOOKUP with FIND and MID: =IFERROR(VLOOKUP(MID(TRIM(B2),FIND("@",TRIM(B2))+1,100),$H$2:$I$9,2,FALSE),""). It gives the same three domains on the sample. FILTER works in Excel 2021; in older versions, use the filter buttons.