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?
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.
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!
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:
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
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...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 10 months agoIt`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.· 10 months agoIt`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.· 10 months agoThis 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.· 1 years agoTo 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.· 1 years agoWill not work for .uk, and for 2015+ domains like .training