Email Address Format Validation in Excel & Google Sheets

This tutorial demonstrates how to check whether email addresses are in the correct format in Excel and Google Sheets.

 

 

When storing lists of email addresses in Excel, a useful feature of data validation is the ability to enter a formula as a custom validation to highlight any invalid email addresses. You cannot check the validity of an email address, or check for any spelling mistakes (such as cm instead of com), but you can check for the existence of the @ sign, make sure there are no spaces or commas in the email address, make sure that the email address does not start with a period, or end with a period, and make sure that the @ sign is before the period.

Custom Formula in Data Validation

1. Highlight the cells in the data validation range.
2. Then, in the Ribbon, go to Data > Data Tools > Data Validation.

 

 

3. Select the Settings tab, and then select Custom from the Allow drop-down box and the type in the following formula in the Formula box:

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

 

 

The formula is made up of seven logical conditions to check and ensure that all seven conditions are met in the email address entered. Namely:

• Make sure no spaces exist.
• Make sure there is an @ sign.
• Make sure that the period (.) is after the @ sign.
• Make sure there are no commas.
• Make sure that the period is not directly after the @ sign.
• Make sure that the email address does not end with a period.
• Make sure that the email address does not start with a period.

If all these conditions are true, then the email address is valid.

4. Select the Input Message tab and remove the check from the Show input message when cell is selected check box.

 

 

5. Select the Error Alert tab and remove the check from the Show error alert after invalid data is entered check box.

 

 

6. Click OK to apply the data validation rule to the selected cells.

Circle Invalid Data

Once you have typed in the email addresses, you can check if they are valid by circling the invalid email addresses using data validation.

1. Highlight the list of email addresses.

 

 

2. In the Ribbon, go to Data > Data Tools > Data Validation > Circle Invalid Data.

 

 

All email addresses that do not match the criteria you set as data validation rules are circled.

 

 

Email Address Format Validation in Google Sheets

Data validation in Google Sheets is very similar to data validation in Excel.

1. First, highlight cells to apply the validation to.

 

 

2. In the Menu, select Data > Data Validation.

 

 

3. Select Custom formula is for the Criteria, and then type in the formula. This is the identical formula to the one you used in Excel:

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

 

 

When you type in invalid email addresses, you get a warning about the violation of the data validation rule.

 

 

There is no capability to circle invalid data in Google Sheets.