• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Validate an Email Address in Excel

Validate an Email Address in Excel

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. Select the range of cells to add the validation to
  2. Click the Data tab on the Ribbon
  3. Click the Data Validation button
  4. On the Settings tab, click the Allow: list arrow and select Custom
  5. Enter the formula below in the Formula box
=AND(FIND("@",A2),FIND(".",A2),ISERROR(FIND(" ",A2)))

Validate an email address using Data Validation

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.

  1. Click Ok

Related Posts:

  • Moving average in Excel thumbnail
    Moving Average in Excel
  • IMAGE function in Excel
    Excel IMAGE Function - Insert Images from a Cell Value
  • N Functions in Excel thumbnail
    N Function in Excel
  • SWITCH function feature
    Excel SWITCH Function - Nested IF Function Alternative

Reader Interactions

Comments

  1. Bruce Nutting says

    28 October 2014 at 8:44 pm

    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.
    Bruce

    Reply
    • computergaga says

      29 October 2014 at 6:54 am

      You will need to highlight all the cells you want to validate. Then A2 represents the first cell in that selection.

      Reply
  2. C. says

    8 June 2016 at 11:39 pm

    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!

    Reply
    • computergaga says

      9 June 2016 at 6:47 am

      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.

      Reply
  3. Clint says

    24 December 2016 at 6:02 pm

    Only partially validation it would appear. You check for presence of spaces in the address as an error but what about the presence of commas? That too is an invalid character.
    The most common error we see is the person entering the address enters the ending domain type (using com as an example) as ,com rather than as .com.

    Reply
    • computergaga says

      28 December 2016 at 9:33 pm

      Yes, validating for the presence of a comma can be done in the same way. This can be used to validate against any invalid characters.

      Reply
  4. Shiva says

    3 November 2017 at 1:19 pm

    Hi Sir,
    I have a List of around 1000 Email-IDs. All are Validated syntax-wise. But, they appear to be Not Genuine when I try to send Email to all of them in one shot. Please help me resolve this issue.

    Reply
    • computergaga says

      6 November 2017 at 6:05 am

      In Excel we can only validate them syntax wise to check if they follow certain rules. You will need to find some other software online to check if they are not ‘fake email ids’.

      Reply

Leave a Reply Cancel reply

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

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·