Create a Custom Autofilter to Find Invalid Email Addresses
Let’s use these two rules to create a custom autofilter in Excel. This will be a good way to do the first pass of our email list and remove the majority of invalid email addresses.
Locate the email address column and select the header of that column
Next, open the Data menu and click Filter. You should see a dropdown icon appear to the right of your selected column header.
Open the dropdown in the column header, and click Text Filter. This will open a sub-menu. In that menu, choose Custom Filter…
A pop up will open with the Custom Autofilter dialogue inside it. In the dropdown on the left, choose Does not contain, and in the input box to the right of the dropdown, type the ‘@’ symbol. Next, check the ‘Or’ radio button to bring up another filter parameter. Create the second parameter the same way you did the first, choosing Does not contain and the ‘.’ symbol. Click OK.
Excel will filter the column and only show you only email addresses that match these two criteria (in other words, the invalid email addresses.)
Use Data Validation to Find Invalid Email Address
The data validation method is slightly more complicated, but it will catch more invalid addresses and will also flag emails for you in real time as you type them into the spreadsheet.
Click the letter header above the column that contains your emails to highlight the entire column of addresses.
From the Excel Ribbon at the top of the application, click Data. This will open a panel with four subpanels. In the Data Tools subpanel, click the triangle next to Data Validation.
A popup containing the data validation rule dialogue will open. From the Allow dropdown menu, select Custom, and in the Formula field, paste the following code:
Click Okay. Next, click the small triangle next to Data Validation again and select Circle Invalid Data.
The application may take a few moments if your email list is very large. Eventually, the invalid emails will be circled in red.
Finding Invalid Emails Using Bulk Email Validation
The above methods are fine for isolating the majority of syntax errors and typos. However, they will not help you find email addresses that have been abandoned, whose top-level domains have ceased use, that is spam, or have invalid MX or SPF records.
The only way to weed out these types of invalid email addresses is to use a third-party service like AbstractAPI’s Free Email Validator. The API has an endpoint that can be used to validate emails as they come in on your landing page or website, and it also provides the option to upload a CSV file containing a list of email addresses for bulk validation.
Let’s take a look at how to do that now.
Get Started With the API
Before you can use the API, you’ll need to sign up with an email address and password to get an API key. The key authenticates you to the API and allows you to make requests. Don’t worry: the API is completely free and will not send you spam or updates. Go to the API homepage and click on the blue "Get Started" button.
If you've never used AbstractAPI before, you'll be asked to create an account using your email address and a password. If you have used the service before, you may need to log in.
You'll land on the API dashboard, which contains links to documentation and pricing, and your API key.
Click "Bulk upload (CSV)" on the left side to be taken to the bulk upload page.
Click the blue "Upload CSV" button. A dialogue box will open with a file browser in it.
Using the file browser, choose the CSV file that contains your mailing list and click "Upload CSV." Depending on the size of your mailing list, you may see a loading indicator while the upload process. Once the file has finished uploading, you’ll see a success box telling you that the file was received.
The results will be emailed to the address from your AbstractAPI account.
If the file is quite small, you should receive the email almost immediately. For larger files, it may take a few minutes.
Inside the email, you'll see a button to download the CSV file that contains your results. You will also be provided with a link to a hosted version of the file. This can be used to share your results with team members.
You can open the file in any spreadsheet program, including Excel.
The file will contain a quality score for each address, and values that indicate whether the address is formatted correctly, whether it is a free email, a disposable email, a role-based account, a catchall email, or whether domain MX records were found, and whether the SMTP is valid. It will also tell you whether or not the address can be delivered.
Conclusion
Purging invalid email addresses from your mailing list is easy using Excel filters and data validation. However, these methods are not as robust as using a dedicated third-party email validation service like AbstractAPI. These validation services can tell you not only if an address is spelled correctly, but whether it can actually be delivered or not.
FAQs
How do I validate an email address?
There are many ways to validate an email address, including checking the syntax against a regular expression, looking up the MX and SPF records of the email domain, pinging the server to assess deliverability, and checking the address against a blacklist of known spam accounts.
Many services out there provide APIs to run these checks against bulk email files, helping you to filter the invalid email addresses out of your mailing lists.
How do I validate an address in Excel?
To validate an email address in Excel, you can use filters and data validation. Filters are an easy way to add rules against which every entry in a column will be checked. When the filter is applied, only rows that match the filter rules will be shown.
Using data validation is a little more complicated, but is a slightly more robust method of validating email addresses. You can add a custom function to search strings inside the cells and highlight strings that do not match the formula rules.
How do I know if an email address is invalid?
It’s impossible to tell whether an email address is invalid simply by looking at it. The best way to determine if an address is invalid is to run it through a dedicated service that checks for invalid email addresses. These services check MX records, top-level domains, deliverability, and known spam blacklists to weed out invalid addresses.
One simple way to check for an invalid email address is to look for the ‘@’ symbol and ‘.’ symbol. An email address must contain at least these two things in order to be considered valid. However, this alone is not enough to tell you whether or not an address is valid.
