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
- Select the range of cells to add the validation to
- Click the Data tab on the Ribbon
- Click the Data Validation button
- On the Settings tab, click the Allow: list arrow and select Custom
- Enter the formula below in the Formula box
The AND function is used to test three conditions and ensure that all three conditions are met for the entry to be valid.
The FIND function is used to check the existence of the @ symbol and the period (.). A separate FIND function is used for each character. If found, the function returns a number indicating the characters position. The returned number is equal to the value of TRUE in a logical expression.
The ISERROR function is used along with another FIND function to check if an error is returned when searching for spaces in the text. If an error is returned, then spaces were not found and the address is ok.
- Click Ok