Search code examples
regexexcelvbaexcel-formulaemail-validation

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

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?

Solution

  • 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: enter image description here

    • 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 enter image description here

    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: Result