Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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. #1
    Board Regular Chris72's Avatar
    Join Date
    Jun 2007
    Location
    Perth - Western Australia
    Posts
    115

    Default 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. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,542

    Default 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))))
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,542

    Default Re: Check E-Mail address entry is valid ???

    actually, this is simpler
    =ISNUMBER(FIND(".",A1,FIND("@",A1)))
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,470

    Default 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
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  5. #5
    Board Regular Chris72's Avatar
    Join Date
    Jun 2007
    Location
    Perth - Western Australia
    Posts
    115

    Default Re: Check E-Mail address entry is valid ???

    Johnmo1 - BEAUTIFUL WORK!

  6. #6
    Board Regular Chris72's Avatar
    Join Date
    Jun 2007
    Location
    Perth - Western Australia
    Posts
    115

    Default 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. #7
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,470

    Default Re: Check E-Mail address entry is valid ???

    My wife is the Princess, I am the pauper.
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  8. #8
    New Member
    Join Date
    Mar 2008
    Location
    Crosby, Texas
    Posts
    29

    Default 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. #9
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,470

    Default 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.
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  10. #10
    New Member
    Join Date
    Mar 2008
    Location
    Crosby, Texas
    Posts
    29

    Default Re: Check E-Mail address entry is valid ???

    No doubt about it!

    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 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com