Typos Can Infest Your Database With Invalid Emails

Emails are excellent means of interacting with and reaching out to clients. But, having bunch of invalid emails in your database (e.g. spreedsheets) is not a positive thing for your business. There are numerous causes that explain the existence of such emails in databases, and the most common among them are typos.

Email typos are products of human error that occur when clients or employees key-in personal details in a website or software. Since prevention is better than cure, we recommend the use of automated validation in any system that request personal information. This way, users are alerted whenever they provide false emails. But since we are writing this article anyway, we assume that the milk is spilled already. Therefore, we would like to share with you a simple technique that allows you to easily manage emails in your database (Excel spreadsheet for now).

The screenshot above shows a real example of invalid emails found in spreadsheets. For various reasons, irrelevant information such as phone number or website is provided instead of an email (the decision of removing or keeping such information belongs to you). In other scenarios, characters such as 2, & or dot are confused for @.

To exposing these emails from thousands of records and edit them easily, you will need to go through the following few steps:

1. Open the Excel spreadsheet you want to clean (e.g. customer contacts, client records).

2. Create a new column beside the email one and call it “Email validation” or any name you like.

3. Copy and paste the following formula in the first cell of the email column:

=AND(NOT(ISERROR(FIND(\”@\”,B2))),NOT(ISERROR(FIND(\”.\”,B2))),ISERROR(FIND(\” \”,B2)))

B is the name of the email column; in your spreadsheet it might be C, D or something else, so replace the B2 in the formula above with your column name.

4. Now the formula will perform the validation and populate the column with

TRUE or FALSE corresponding to valid or invalid emails.

5. With the records validated, we will only need to filter out the valid emails, leaving the invalid ones to deal with. Check the “Filter” option in “Data” tab then go to your “Email validation”, click on the dropdown button at the right to view the filter options and check the FALSE filter only. Click OK to proceed.

6. Once done filtering, your spreadsheet will only show records with invalid emails. Scour through the emails to find and fix typos similar to the example shown earlier. That’s it! We hope that you will find this tutorial a useful resource for managing the emails in your database.