Excel is a great program for evaluating data. In some cases, it creates hyperlinks for email addresses. In the case of my friends, some of the bad entries were the result of how the info was captured from their website. Other times, typing errors were made or customers intentionally provided incorrect email addresses.
Email Address Rules
I’m sure there may be a way someone could write Excel code to validate email addresses using regular expressions or syntax rules. I didn’t want to take that path, as I’m not a coder. In doing some research, I also noticed that there were more possibilities for valid email addresses than I realized. Here are a few examples:
1. They can contain the “+” sign even though many systems disallow this character. For example, Google allows you to use the plus sign for their version of disposable email addresses.
2. They can contain multiple periods.
3. The top-level domains are not always 3 letters like .com, .net or .org. All country codes are two letters and then there are TLDs like .travel or .museum
4. They can contain hyphens.
If you want to get the specifics, there is a standard RFC 822 document for internet messaging.
After scrolling through these Excel address lists, I settled on two rules, which could identify most of the bad email address entries. These aren’t email addressing laws or absolutes. Instead, these are simple rules to knock out 95% of the badly formatted addresses.
The email address had to contain the “@” sign
The email address had to contain at least 1 period.
The nice thing about these two rules is I could have Excel find the invalid emails using the Filter command. Below you will see a screen snap of the type of entries that were on these Excel spreadsheets. You can also see where Excel will create hyperlinks on improper entries.
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…).
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.
5. Click OK.
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
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.
Related Excel Tutorial:
Last Updated (Sunday, 24 October 2010 15:42)