Email ID Syntax Validation in Excel

.

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

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

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

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 🙂
 

Download Now

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

1 Comment

  1. Azam

    Mr Vishwa ,
    Thanks a lot for your explanationn.
    In My CSV File (openOffice Calc) i m trying to validate email id in cell E2
    but when i m running your macro code and Error Message Comes
    “Wrong Number of Parameters”
    Kindly provide some suitable solution

    Thanks again

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest