Email Address Format Validation in Excel & Google Sheets

This tutorial will demonstrate validating that email addresses are in the correct format in Excel and Google Sheets.

 

EmailDataValid Intro

 

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 in order 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, select Data > Data Tools > Data Validation.

 

EmailDataValid Ribbon

 

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

 

EmailDataValid Custom

 

The formula is made up of seven logical conditions which will check to ensure that all seven conditions are true 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.

 

EmailDataValid InputMessage

 

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

 

EmailDataValid ErrorAlert

 

6. Click OK to apply the Data Validation to the selected range of 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.

 

EmailDataValid HighlightEmails

 

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

 

EmailDataValid CircleData

 

All email addresses that do not match the criteria that you have set in the Data Validation will be circled.

 

EmailDataValid Circled

 

Email Address Format Validation in Google Sheets

Data Validation in Google Sheets is very similar to Data Validation in Excel.

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

 

EmailDataValid GS Range

 

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

 

EmailDataValid GS Menu

 

3. Select Custom formula is in 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)<>".")

 

EmailDataValid GS Criteria

 

When you type in invalid email addresses, you will be warned about the violation of the data validation rule.

 

EmailDataValid GS Error

 

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