Inside Microsoft Excel

Ensure that your email addresses follow the correct formatby Sean Kavanagh

Inside Microsoft Excel

by Sean Kavanagh

You can verify that email addresses are in a valid format using Excel’s Data Validation feature combined with a custom formula.

To ensure that you enter a valid email address:

  1. Select the data range that will contain the email addresses. We’ll select range D2:D10.
  2. Choose Data | Validation from the menu bar to open the Data Validation dialog box.
  3. Select Custom from the Allow dropdown list.
  4. In the Formula text box, enter the formula¬†=AND(FIND(“@”,D2,1)>1,(RIGHT(D2,4)=”.COM”),(RIGHT(D2,5)<>”@.COM”)), as shown in¬†Figure[…]

Subscribe to Inside Microsoft Excel

(get full access to archives and more)