How To Check If The Email Address Is Valid Or Not In Excel?

Planning to send emails but worried some addresses may be entered incorrectly in your spreadsheet?

When mailing your clients, you may often see more than one invalid email. Thanks to Microsoft Excel, you can identify these email addresses and correct them as required. This process is also known as data cleansing.

Cleaning your data ensures that your spreadsheet only contains relevant information so you can easily move on to the next task. The goal is to repair any data that is wrong, incomplete, poorly structured, duplicated, or even unrelated to the data set's mission.

Find Invalid Email Address Using Custom Auto Filter

Below is a spreadsheet containing emails from clients. On a cursory glance you may notice Excel does not generate hyperlinks for incorrect inputs. By modifying excel's filter command, you can sort out all the valid and non-valid email addresses. Here is how −

Step 1 − Click the column header that contains all the email addresses.

Step 2 − Go to the "Data" tab and click on the Filter option from the toolbar. You will see a down arrow icon appear on the first cell of the selected column.

Step 3 − Click the drop-down icon → select "Text Filters" → From the submenu, select the "Custom Filter" option. In both the filter criteria of the Custom AutoFilter dialogue box, select the "does not contain" option.

Step 4 − Enter the two filter criteria in the Custom AutoFilter dialogue box. Make sure you select the Or radio button.

The first is "@," and the second is "."

This instruction is given because all email addresses have the "@" symbol and at least one period "."

Click okay to see the filtered list of invalid emails.

Step 5 − You can highlight the incorrect emails and click the "Filter" icon on the top right corner of the selected column. In the original spreadsheet, invalid emails will be highlighted with the colour after all filters have been cleared.

After clearing all filters, we can see the incorrect emails on the original list.

Find Invalid Emails Using Data Validation

This is a more advanced solution, but this will result in greater accuracy of invalid entries being found.

Step 1 − Select the column with all the email addresses, in this case, column B.

Step 2 − Navigate to the "Data" tab, and click on the Data Validation option from the toolbar.

Step 3 − After the Data Validation dialogue box opens, in the allow section, click on custom and paste the following syntax in the formula section

=AND(ISERROR(FIND(" ",A1)),LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))=1,IFERROR(SEARCH("@",A1)<SEARCH(".",A1,SEARCH("@",A1)),0),NOT(IFERROR(SEARCH("@",A1),0)=1),NOT(IFERROR(SEARCH(".", A1,SEARCH("@",A1))-SEARCH("@",A1),0)=1),LEFT(A1,1)<>".",RIGHT(A1,1)<>".")

Click okay.

Step 4 − You will need to do an additional step to see the results. After clicking okay, click on "Data Validation" again and select "Circle Invalid Data" to see mark the invalid email IDs.

Your final results will appear like this.

Conclusion

With these two tricks in this tutorial, we can easily check which emails are valid or not. Check out more such interesting Excel tutorials to level up your skills.