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: firstname.lastname@example.org
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:
Press ALT + F11 to go to the VBE (VB Code Editor)
From the Top-Left, right click on any Sheet and Add a New Module
Paste the above code in that module (in Right side Pane as shown in the below picture)
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)
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)
Follow the below steps to achieve above:
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
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P
AND GET A FREE!
E-BOOK FOR EXCEL VBA BEGINNERS
DON'T MISS ANY NEW ARTICLE !
Get your FREE! E-Book & Have Every New Article Delivered Straight To Your Email-Box