Dear LEM users,
 
I got quite a few questions regarding How to enforce Email ID syntax validation in Excel. It could be in a particular cell or any textbox etc. So i thought of putting this small excel VBA tip which may help a lot in enforcing this validation. This small validation trick will make your excel intelligent and your BOSS happy 🙂
 

Email ID syntax validation in an Excel Cell

Suppose you have a cell to enter email ID ONLY and ofcourse you would like user to enter a valid email ID there. To verify that you would like to place a syntax validation in place so that user is notified on entering a an invalid email ID. (Only Syntax validation here) Example: abc@xyz.com
 
I have created a function which does the syntax validation of any text entered as an email ID.
 

Public Function IsValidEmail(rng As Range) As Boolean
    If Trim(rng.Value) Like "?*@[!.]*.[!.]*" Then
        If Not rng.Value Like "*@*@*" Then
            IsValidEmail = True
        End If
    End If
End Function

How to use the above function – Method 1

The above function can be used in many ways. But here I am explaining to use the above function in two ways.
Follow the below steps to do so:

Step 1

Press ALT + F11 to go to the VBE (VB Code Editor)

Step 2

From the Top-Left, right click on any Sheet and Add a New Module

Step 3

Paste the above code in that module (in Right side Pane as shown in the below picture)
 

Pic 1 - Function - Email Syntax Validation

Step 4

Now in your excel sheet you can use IsValidEmail as a formula. It takes cell address as Input. This formula validates the email ID entered in that cell and returns TRUE/FALSE accordingly. (Refer below picture)
 

Pic 2 - Fformula - Email Syntax Validation

How to use the above function – Method 2

In this method, I will tell you, How to implement an auto-validation in cell as soon as an email ID is entered in it. User receives an error message as soon as he/she enters an email ID with an incorrect syntax. ( Refer the below pic)
 

Pic 3 - Email Syntax Validation

Follow the below steps to achieve above:

Step 1

From the Top-Left side double click on the Sheet Name where your cell is there on which you want to implement this validation. In right side Code Pane, paste the below code
 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim msg As Boolean
' "$B$15" is the cell address where email ID
' is supposed to be entered user
    If Target.Address <> "$B$15" Then
        Exit Sub
    Else
        msg = IsValidEmail(Target)
' C15 is the cell address where you want to display
' the error message on Entering the wrong email ID.
        If msg = False Then Range("C15").Value = "Incorrect Email ID"
        If msg = True Then Range("C15").Value = ""
    End If
End Sub

Now you are done :). As soon as you enter a wrong email ID in Cell B15 then you will get an error message displayed in Cell C15 as shown in Pic 3

Download the excel with code. Play around with the code which will make you awesome in Excel Macro…Happy Email Validation 🙂