How do can I filter email addresses in Excel 2007/10

  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.excelemail1
  3. From the drop-down menu, select (Custom…).excelemail2
  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.excelemail3
  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.