Search Posts

Finde forkerte email

The email address had to contain the “@” sign
The email address had to contain at least 1 period.


To filter for invalid email addresses in Excel,

1. From the Data menu, select Filter and then AutoFilter. This will add a drop-down list box to each of your columns.
2. Click the down arrow control in the top right of the column containing your email addresses.
3. From the drop-down menu, select (Custom…).
Excel custom filterExcel custom filter
4. In the Custom AutoFilter dialog, enter in two filter criteria using the “does not contain” selection. Make certain to use the Or radio button option.
Excel custom autofilterExcel custom autofilter
5. Click OK.
Filtered email list of bad addressesFiltered email list of bad addresses
Once you click OK, Excel will show the emails that don’t have either the @ sign or a period. These are the problem records. You can either delete them or try to make corrections. The rules aren’t perfect as you may still get some incorrect ones slip through such as anne.h@examplecom. In this example, both the period and "at sign" are present, but it is still invalid since a second period is needed between example and com.
After doing this exercise, I did see some other patterns. For example, some people would type some combination of “do not send email”. This tends to happen if the input form doesn’t do field validation. Other times, the reverse would happen where people used “shift 2” or [at] instead of the “@ sign”. This is a tactic some people use online to prevent email bots from scraping their address.

Other Steps You Might Take

I also saw a number of emails with spaces such as anne I’m not certain if spaces are legitimate, but I seem to recall AOL using them at one time. However, many email systems will not accept spaces in an email address. One way to correct this issue is to use the highly recommended and free Excel add-on ASAP Utilities, which has a feature to trim leading, trailing and embedded spaces.
The other item I noticed was many duplicate email addresses. Either people had entered the information multiple times or perhaps the address was shared by two or more people. You should take steps to remove these duplicates, as the recipients do not want multiple copies. Some may even get mad enough to flag your email as spam. Again, ASAP Utilities can help with this issue as it can color code duplicate records within a column.
The above method for finding bad email addresses isn’t perfect. What it does do is give you an easy method to find the badly formatted addresses so you can act on them in a timely way. By removing these bad ones, you also prevent someone from getting those annoying bounce back messages from ISPs telling you the user doesn’t exist.

Tegn abonnement på

BioNyt Videnskabens Verden ( er Danmarks ældste populærvidenskabelige tidsskrift for naturvidenskab. Det er det eneste blad af sin art i Danmark, som er helliget international forskning inden for livsvidenskaberne.

Bladet bringer aktuelle, spændende forskningsnyheder inden for biologi, medicin og andre naturvidenskabelige områder som f.eks. klimaændringer, nanoteknologi, partikelfysik, astronomi, seksualitet, biologiske våben, ecstasy, evolutionsbiologi, kloning, fedme, søvnforskning, muligheden for liv på mars, influenzaepidemier, livets opståen osv.

Artiklerne roses for at gøre vanskeligt stof forståeligt, uden at den videnskabelige holdbarhed tabes.

Leave a Reply