VALIDATE EMAIL IN EXCEL

If you have a list of email addresses in Excel that you need to validate follow proper format, or would like to prevent other Excel users from entering invalid addresses in a column, use Data Validation. By using a custom formula as the validation criteria, email address validation can be done. To get started, with the cells you need validated selected go to Data > Data Validation.

https://youtu.be/xN9sI1bHRv0

In the data validation box, choose Custom under the Allow drop down and enter the following formula:

=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)<>".")

Be sure to replace “A1” with the first cell in the range of cells you selected. The relative reference will automatically adjust to the cell it is validating.

Now you can go to Data Validation > Circle Invalid Data to find invalid data, and you won’t be able to enter an invalid email address in the column!