How to validate email addresses in a column of worksheet?

As we all known, a valid email address is composed of three parts, the user name, the "at symbol" (@), and the domain. Sometimes, you just allow others to enter only the email address format text into a specific column of worksheet. Is it possible to achieve this task in Excel?

Validate only email addresses in a column with Data Validation function

Validate only email addresses in a column with Data Validation function

In Excel, the powerful feature Data Validation can help you to quickly validate only email addresses entered in a column of cells, please do as follows:

1. Select the cells that you want to only allowed to type with email addresses format, and then click Data > Data Validation > Data Validation, see screenshot:

2. In the Data Validation dialog box, under the Settings tab, choose Custom from the Allow drop down, and then enter this formula: =ISNUMBER(MATCH("*@*.???",A2,0)) into the Formula text box, see screenshot:

Note: In the above formula, A2 is the first cell of the column that you want to validate the cells.

3. Then click OK button, now, when you enter texts which are not email addresses format, a warning message box will pop out to remind you, see screenshot:

Demo: Validate only email addresses in a column with Data Validation function

Recommended Productivity Tools

Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel