In this article, we will show you how to validate email addresses in Excel using the Data Validation feature and the IF function. Simply follow the process below.
The Data Validation feature of Excel is an effective tool for checking the integrity of email addresses in your spreadsheet. Follow the steps below to validate email address in Excel.
Begin typing or copy and pasting email addresses you wish to validate into a column in Excel. Ensure each email is in its own cell for effective validation.
Select the cells with the email addresses by clicking and dragging over them. Then, go to the 'Data' tab on the top menu and click on the 'Data Validation' button in the 'Data Tools' section.
In the Data Validation dialog box, choose 'Custom' from the 'Allow' dropdown list. This step enables you to define a specific validation rule.
Type in the formula: `=AND(ISNUMBER(SEARCH("@", A1)), ISNUMBER(SEARCH(".", A1)), NOT(ISNUMBER(SEARCH(" ", A1))))`. Replace A1 with the cell reference of your first email address. This formula checks for '@' and '.', and ensures no spaces are present – typical traits of valid emails. Click 'OK' to apply the validation rule.
Test the validation rule by entering both valid and invalid email addresses in the selected cells. Invalid emails will prompt an error message.
To create a custom error alert, reopen 'Data Validation' and select the 'Error Alert' tab. Here, you can craft a specific message that appears for invalid email inputs.
Validating email addresses in Excel using the IF function is an effective way to check if the data in your spreadsheet meets specific email format criteria. Follow the steps below to create a formula using the IF function to validate emails.
Input the email addresses you want to validate into a column. Each email should be in a separate cell.
In the cell next to your first email address, write the IF function. Use the formula: `=IF(AND(ISNUMBER(SEARCH("@", A1)), ISNUMBER(SEARCH(".", A1)), LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))=1), "Valid", "Invalid")`. Replace `A1` with the reference of the email address cell.
Drag the corner of the cell with the formula to copy it down the column, applying it to all email addresses. The formula will adjust automatically for each cell.
The formula will display an error message for email addresses that do not fit the standard format.
We hope that you now have a better understanding of how to validate email address in Excel using the Data Validation feature and the IF function. If you enjoyed this article, you might also like our article on how to compress an Excel file for email or our article on how to send part of an Excel spreadsheet by email.
