Validate an Email Address in Excel

0 Flares 0 Flares ×

If you collect email addresses in your spreadsheet. You may want to validate an email address in Excel.

Using Data Validation you can check the syntax of an email address to ensure it is legitimate. It cannot check the existence of an address, or who it belongs to. But it will ensure the existence of the @ symbol, a period (.) and no spaces in the address.

Validate an Email Address

  1. Click the Data tab on the Ribbon
  2. Click the Data Validation button
  3. On the Settings tab, click the Allow: list arrow and select Custom
  4. Enter the formula below in the Formula box

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Validate an email address using Data Validation

The FIND function is used to check the existence of the @ symbol and the period (.).

The ISERROR function is used to check if an error is returned when searching for spaces in the text. If one is returned then spaces were not found and the address is ok.

  1. Click Ok

Other Data Validation Examples

Prevent duplicates in Excel

Validate uppercase text

4 thoughts on “Validate an Email Address in Excel

  1. Well I was not able to get the formula to work and I copied, pasted and deleted the url information. I am assuming that A2 represents the cell you are validating.


  2. Thanks for sharing! So is there a way to copy paste a list of email addresses to validate them on excel? I noticed this formula only works when you type them in one by one. Thanks again!

    • If you set the validation criteria to a range e.g. A1:A100. Then paste data into that range. It won’t auto check it, but you should be able to click the list arrow on the Data Validation button and select Circle Invalid Data.

Leave a Reply

Your email address will not be published. Required fields are marked *