Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

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


Extract Email Address from text strings:

Kutools for Excel’s Extract Email Address can help you extract the email addresses from the text strings quickly and conveniently.

doc extract emails-1

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


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:

doc validate email address 1

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.

doc validate email address 2

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:

doc validate email address 3


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


Recommended Productivity Tools

Office Tab

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

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    AM`s · 10 months ago
    It`s great you can validate straightly in Excel, however, it doesn`t always work properly. So, I would recommend using online services for that. There is a lot to choose from, I, for example, use proofy.io.
  • To post as a guest, your comment is unpublished.
    James · 10 months ago
    It`s great to be able to validate emails straight in Excel, however, it doesn`t work out effectively sometimes. So, I think that the online tools may help. For example, proofy.io, which I, personally use.
  • To post as a guest, your comment is unpublished.
    alex · 10 months ago
    This doesn't validate emails at all.... I am still able to place spaces and returns in the field (in the middle and at the end) which negates the point for me.
  • To post as a guest, your comment is unpublished.
    Jim · 1 years ago
    To correct this so it works for all domains, use: =ISNUMBER(MATCH("*@*.?*",A2,0)). Then it supports 2-letter domains such as ".ca" as well as complex domains such as ".co.uk" and "server1.company.com".
    • To post as a guest, your comment is unpublished.
      Bethwel · 5 months ago
      Wow ! Great this works best
    • To post as a guest, your comment is unpublished.
      John · 10 months ago
      Thanks - I also had an issue with 2-letter domains that this fixes.
  • To post as a guest, your comment is unpublished.
    IK · 1 years ago
    Will not work for .uk, and for 2015+ domains like .training