If you need a simple way to validate email addresses in Microsoft Excel, this basic formula might come in handy, particularly if you have multiple email addresses to validate.
This is really useful when you have an export of contact information, say from a CRM system or customer order system where the data input has not been verified. You might need this if you wanted to export contacts to your email marketing platform or to a third party customer review system, where invalid records would invalidate the upload.
Here’s the formula:
=AND(NOT(ISERROR(FIND("@",*))),NOT(ISERROR(FIND(".",*))),ISERROR(FIND(",",*)),ISERROR(FIND(" ",*)))
All you need to do is replace the * with the reference to the target cell containing the email address (such as A1)
The formula will check that the email address field contains both an at sign (@) and a period (.), then check that no commas (,) or spaces ( ) are present. It then returns ‘TRUE’ if the email address is valid ,or ‘FALSE’ if invalid.
It’s then down to you to fix the email address. In most cases it’s a simple fix, however, it’s not foolproof, but it should help you identify the most common errors.
For more helpful tips like these check out the ‘Tips & Tricks’ section.
Leave a Comment