If we have a list of email addresses, like about 20,000 and we want to check if they are valid i.e. (valid format with an email id followed by ‘@’ and then domain name) then this macro can help us in doing this with a click.

Example sheet also available at end for Download

We will use VBA and the regular expression function (Regex).

To use Regex we need to do a few things beforehand.

Add VBA reference to "Microsoft VBScript Regular Expressions 5.5"

Ø For this select the “Developer tab”. If you don’t have it then (for Office 2010) go to File -> Options ->Customize Ribbons ->Popular Commands in Choose commands from -> Click on Developer tab box in customize the ribbon box.

Ø Now you can see the Developer tab on top, click on it and then on Visual Basic.

Ø Go to Tools -> References and select Microsoft VBScript Regular Expressions 5.5. Now you are set to run the Regex Macros.

Below is the VB Code of Macro.

Sub amiq()

Dim strPattern As String: strPattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
Dim regEx As New RegExp
Dim strInput As String
Dim r As Integer

For r = 2 To Selection.Rows.Count
If strPattern <> "" Then
strInput = Cells(r, 1).Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With

If regEx.Test(strInput) Then

Cells(r, 2).Value = "Valid Email Address"
Cells(r, 2).Value = "Not Valid"
End If
Next r
End Sub

I have found this Regex pattern to check mail address validity from totorialspoint.

Please download the example Macro enabled workbook to see how it works. You just need to paste the list of mails in the first column then select and press the button. The second column will show their validity status.
[null, null, null, null, {"document_url": "http://amiqexcel.blogspot.com/2015/10/if-we-have-list-of-email-addresses-like.html", "unformatted_src": "https://3.bp.blogspot.com/-fWfd_DnnDc4/VheR0EwR9VI/AAAAAAAAAQ4/B-5g52hgH2A/s640/emailvalidate.jpg", "src": "https://3.bp.blogspot.com/-fWfd_DnnDc4/VheR0EwR9VI/AAAAAAAAAQ4/B-5g52hgH2A/s640/emailvalidate.jpg", "formatted_filename": "emailvalidate"}, null, null, null, {"document_url": "http://amiqexcel.blogspot.com/2015/10/if-we-have-list-of-email-addresses-like.html", "unformatted_src": "//lh5.googleusercontent.com/-jNXtj1c7kvQ/AAAAAAAAAAI/AAAAAAAAAJw/pyn0JWmcHSc/s80-c/photo.jpg", "src": "http://lh5.googleusercontent.com/-jNXtj1c7kvQ/AAAAAAAAAAI/AAAAAAAAAJw/pyn0JWmcHSc/s80-c/photo.jpg", "formatted_filename": "photo", "alt_text": "My photo", "rendered_width": 80, "rendered_height": 80}, null]