Email Address Format Validation in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on January 4, 2023

This tutorial demonstrates how to check whether 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 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.

 

EmailDataValid Ribbon

 

  1. 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 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.

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

 

EmailDataValid InputMessage

 

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

 

EmailDataValid ErrorAlert

 

  1. 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.

 

EmailDataValid HighlightEmails

 

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

 

EmailDataValid CircleData

 

All email addresses that do not match the criteria you set as data validation rules are 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 cells to apply the validation to.

 

EmailDataValid GS Range

 

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

 

EmailDataValid GS Menu

 

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

 

EmailDataValid GS Criteria

 

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

 

EmailDataValid GS Error

 

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

AI Formula Generator

Try for Free

See all How-To Articles