I Have An Excel Sheet With Few Fields Including Email Address?

I Have An Excel Sheet With Few Fields Including Email Address?

I suggest using regular expressions to find incorrectly formed email addresses. The user-defined function in the code block below returns TRUE if the address is valid, and FALSE if it is not. You may use it with a worksheet formula testing an address in cell A2 like. =ValidEmailAddress(A2) The code for the user-defined function should be stored in a regular module sheet, just like a recorded macro. Do not put the code in ThisWorkbook or the code pane for a worksheet. Use the Insert…Module menu item if you don’t already have a module sheet in your workbook. Make sure you save the workbook with .xlsm, .xlsb or .xls file extension. If you save it with the default .xlsx file extension, the code will be removed from the saved copy. Also make sure you enable macros when you open the workbook. If you don’t, you’ll see a #NAME? error value in your worksheet formulas. Function ValidEmailAddress(Email As String) As Boolean Static RegEx As Object If RegEx Is Nothing Then Set RegEx = CreateObject("VBScript.RegExp") With RegEx 'Pattern copied from VB.net example in https.//emailregex.com .Pattern = "^[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})$" .IgnoreCase = True .Global = True ValidEmailAddress = .Test(Email) End With End Function Note that the general specification for email addresses permits some unusual variations that would make you scratch your head if you saw them. As a result, there really isn’t any generally accepted test that is 100% guaranteed to work with any valid address. The above code uses one of the suggestions on Email Address Regular Expression That 99.99% Works.