How to use an excel formula to validate an customized email address?

Question

I'm very new to excel VBA macro and I just want to right an excel formula to validate an email which has been customized. For an example the email that I'm finding, must end up with '@gmail.com' and must reject following mail items;

1. [no word]@gmail.com
2. [space]@gmail.com
3. [email protected][any word]
4. [email protected]
5. xxx@gmail/.com
6. xxx@gmail/com

To validate the email address I have written a regex as follows;

^([a-zA-Z0-9_\-\.]+)@(\b(gmail)\b)+(\.\b(com)\b)$

I tried different ways to apply these regex in to an excel formula like =MATCH but it did not work. I need to complete this email validation using an excel formula only.

I'm grateful if someone can help me on this?

Answer 1

Before you use the regex inside excel; you need to setup VB inside to be able to use it. Please follow the below steps:

Steps to Add VBA reference to "Microsoft VBScript Regular Expressions 5.5

• Select "Developer" tab (I cannot find this tab what to do?)

• Select "Visual Basic" icon from 'Code' ribbon section

• In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.

• Select "References"

• Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.

• Click "OK".

• Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu. The screenshot may look like:

• Now; inside the module; paste the below function.

Function simpleCellRegex(MyVal As String) As String
    
    Dim regEx As New RegExp

    'Below is the flag options'
    With regEx
        .Global = True
        .Pattern = "^[\w.-]+@gmail\.com$"
    End With

    If regEx.Test(MyVal) Then
        simpleCellRegex = "Matched"
    Else
        simpleCellRegex = "Not matched"
    End If

End Function

Save the whole workbook as Excel Macro-enabled Workbook instead of Excel workbook

That's it. You're all set.

Now go to your excel sheet and inside your formulas tab use the above declared macro. I used

Result:

Answer 2

While you have mentioned what to avoid, you have not really mentioned what exactly to match. It's hard to imagine you'd want to match something like [email protected] or [email protected] and [email protected] so therefor a little bit more secure would be:

^(?!_+(?:[.-]?_*)*)\w+(?:[-.]?\w+)*@gmail\.com$

See the online Demo. It's not perfect but may take care of a number of cases you actually need the match to fail.

To call this as an UDF, you may use:

Function REGEXMATCH(str As String, pat As String) As Boolean
    
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = pat
        REGEXMATCH = .Test(str)
    End With

End Function

You can now call this using =REGEXMATCH(A1,"^(?!_+(?:[.-]?_*)*)\w+(?:[-.]?\w+)*@gmail\.com$")

Note I used late binding on the regexp object. It's a personal preference and may come in handy when you need to distribute the project.