# Check E-Mail address entry is valid ???

This is a discussion on Check E-Mail address entry is valid ??? within the Excel Questions forums, part of the Question Forums category; One cell in my worksheet requires an e-mail address entry. What formula can be derived that checks the validity of ...

1. ## Check E-Mail address entry is valid ???

One cell in my worksheet requires an e-mail address entry.

What formula can be derived that checks the validity of the entry as an e-mail address?

Regards,

Chris....

2. ## Re: Check E-Mail address entry is valid ???

The only criteria I can think of for a valid email address is contains an @ and a . at some point AFTER the @

this should check that criteria....

=AND(ISNUMBER(FIND("@",A1)),ISNUMBER(FIND(".",A1,FIND("@",A1))))

3. ## Re: Check E-Mail address entry is valid ???

actually, this is simpler
=ISNUMBER(FIND(".",A1,FIND("@",A1)))

4. ## Re: Check E-Mail address entry is valid ???

Or via VBA via Regular Expressions...

Public Function IsValidEmail(Value As String) As Boolean
'Originally from Tommy Gun
Dim RE As Object
Set RE = CreateObject("vbscript.RegExp")
RE.Pattern = "^[a-zA-Z0-9\._-]+@([a-zA-Z0-9_-]+\.)+([a-zA-Z]{2,3})\$"
IsValidEmail = RE.Test(Value)
Set RE = Nothing
End Function

HTH

5. ## Re: Check E-Mail address entry is valid ???

Johnmo1 - BEAUTIFUL WORK!

6. ## Re: Check E-Mail address entry is valid ???

FIREFYTR (Zack) - I will try this one a little later when I have put everything to bed.

PS: So who's who in the picture are you the Prince or the lovely Princess?

7. ## Re: Check E-Mail address entry is valid ???

My wife is the Princess, I am the pauper.

8. ## Re: Check E-Mail address entry is valid ???

Another way
Code:
```Public Function GoodEmail(iEmail As String) As Boolean
GoodEmail = IIf(iEmail Like "*@*.*", True, False)
End Function

Formula
=IF(GoodEmail(A3)," ","Please Correct email, it appears to be invalid")```

9. ## Re: Check E-Mail address entry is valid ???

The reason for the regexp is the addition of the special characters check. When you use the * wildcard, you are really leaving the field open for anything to be used, and not all characters are valid in email addresses. Of course there are pros and cons to all solutions.

10. ## Re: Check E-Mail address entry is valid ???

My post is a quick and dirty for normal data entry.
It would not work at all if the e-mail was being entered through other means. I am offering another way other than loading the regexp on each entry.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•