+ Reply to Thread
Results 1 to 5 of 5

email validation?

  1. #1
    GoBobbyGo
    Guest

    email validation?

    is there a built-in function in Excel that tells whether a text string is a
    valid email? (I don't mean whether the address exists or not, but rather if
    it has exactly one "@" in it, then some letters, then a ".", some more
    letters, etc)

    I can build one, but before I do...

    ----------------
    This post is a suggestion for Microsoft, and Microsoft responds to the
    suggestions with the most votes. To vote for this suggestion, click the "I
    Agree" button in the message pane. If you do not see the button, follow this
    link to open the suggestion in the Microsoft Web-based Newsreader and then
    click "I Agree" in the message pane.

    http://www.microsoft.com/office/comm...lic.excel.misc

  2. #2
    Chip Pearson
    Guest

    Re: email validation?

    There is no built-in way to do this. Try

    Dim S As String
    S = "chip@cpearson.com"
    If S Like "?*@?*.?*" Then
    Debug.Print "OK"
    Else
    Debug.Print "Not ok"
    End If



    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com


    "GoBobbyGo" <GoBobbyGo@discussions.microsoft.com> wrote in
    message
    news:2321863F-4F63-4948-BE47-BCD80DE8A211@microsoft.com...
    > is there a built-in function in Excel that tells whether a text
    > string is a
    > valid email? (I don't mean whether the address exists or not,
    > but rather if
    > it has exactly one "@" in it, then some letters, then a ".",
    > some more
    > letters, etc)
    >
    > I can build one, but before I do...
    >
    > ----------------
    > This post is a suggestion for Microsoft, and Microsoft responds
    > to the
    > suggestions with the most votes. To vote for this suggestion,
    > click the "I
    > Agree" button in the message pane. If you do not see the
    > button, follow this
    > link to open the suggestion in the Microsoft Web-based
    > Newsreader and then
    > click "I Agree" in the message pane.
    >
    > http://www.microsoft.com/office/comm...lic.excel.misc




  3. #3
    Chip Pearson
    Guest

    Re: email validation?

    This one is better than my previous reply:

    If Len(S) - Len(Replace(S, "@", "")) = 1 Then
    If S Like "?*@?*.?*" Then
    Debug.Print "OK"
    Else
    Debug.Print "Not ok"
    End If
    Else
    Debug.Print "not ok"
    End If


    "Chip Pearson" <chip@cpearson.com> wrote in message
    news:OEKBW5yYGHA.3740@TK2MSFTNGP03.phx.gbl...
    > There is no built-in way to do this. Try
    >
    > Dim S As String
    > S = "chip@cpearson.com"
    > If S Like "?*@?*.?*" Then
    > Debug.Print "OK"
    > Else
    > Debug.Print "Not ok"
    > End If
    >
    >
    >
    > --
    > Cordially,
    > Chip Pearson
    > Microsoft MVP - Excel
    > Pearson Software Consulting, LLC
    > www.cpearson.com
    >
    >
    > "GoBobbyGo" <GoBobbyGo@discussions.microsoft.com> wrote in
    > message
    > news:2321863F-4F63-4948-BE47-BCD80DE8A211@microsoft.com...
    >> is there a built-in function in Excel that tells whether a
    >> text string is a
    >> valid email? (I don't mean whether the address exists or not,
    >> but rather if
    >> it has exactly one "@" in it, then some letters, then a ".",
    >> some more
    >> letters, etc)
    >>
    >> I can build one, but before I do...
    >>
    >> ----------------
    >> This post is a suggestion for Microsoft, and Microsoft
    >> responds to the
    >> suggestions with the most votes. To vote for this suggestion,
    >> click the "I
    >> Agree" button in the message pane. If you do not see the
    >> button, follow this
    >> link to open the suggestion in the Microsoft Web-based
    >> Newsreader and then
    >> click "I Agree" in the message pane.
    >>
    >> http://www.microsoft.com/office/comm...lic.excel.misc

    >
    >




  4. #4
    GoBobbyGo
    Guest

    Re: email validation?

    I ended up just going with the function:

    =IF(OR(ISERROR(FIND("@",M14)),ISERROR(FIND(".",RIGHT(M14,LEN(M14)-FIND("@",M14))))),"invalid",IF(AND(FIND("@",M14)>1,ISERROR(FIND("@",RIGHT(M14,LEN(M14)-FIND("@",M14)))),FIND(".",RIGHT(M14,LEN(M14)-FIND("@",M14)))>1,FIND(".",RIGHT(M14,LEN(M14)-FIND("@",M14)))<LEN(M14)-FIND("@",M14)),"valid","invalid"))
    "Chip Pearson" wrote:

    > This one is better than my previous reply:
    >
    > If Len(S) - Len(Replace(S, "@", "")) = 1 Then
    > If S Like "?*@?*.?*" Then
    > Debug.Print "OK"
    > Else
    > Debug.Print "Not ok"
    > End If
    > Else
    > Debug.Print "not ok"
    > End If
    >
    >
    > "Chip Pearson" <chip@cpearson.com> wrote in message
    > news:OEKBW5yYGHA.3740@TK2MSFTNGP03.phx.gbl...
    > > There is no built-in way to do this. Try
    > >
    > > Dim S As String
    > > S = "chip@cpearson.com"
    > > If S Like "?*@?*.?*" Then
    > > Debug.Print "OK"
    > > Else
    > > Debug.Print "Not ok"
    > > End If
    > >
    > >
    > >
    > > --
    > > Cordially,
    > > Chip Pearson
    > > Microsoft MVP - Excel
    > > Pearson Software Consulting, LLC
    > > www.cpearson.com
    > >
    > >
    > > "GoBobbyGo" <GoBobbyGo@discussions.microsoft.com> wrote in
    > > message
    > > news:2321863F-4F63-4948-BE47-BCD80DE8A211@microsoft.com...
    > >> is there a built-in function in Excel that tells whether a
    > >> text string is a
    > >> valid email? (I don't mean whether the address exists or not,
    > >> but rather if
    > >> it has exactly one "@" in it, then some letters, then a ".",
    > >> some more
    > >> letters, etc)
    > >>
    > >> I can build one, but before I do...
    > >>
    > >> ----------------
    > >> This post is a suggestion for Microsoft, and Microsoft
    > >> responds to the
    > >> suggestions with the most votes. To vote for this suggestion,
    > >> click the "I
    > >> Agree" button in the message pane. If you do not see the
    > >> button, follow this
    > >> link to open the suggestion in the Microsoft Web-based
    > >> Newsreader and then
    > >> click "I Agree" in the message pane.
    > >>
    > >> http://www.microsoft.com/office/comm...lic.excel.misc

    > >
    > >

    >
    >
    >


  5. #5
    Bob Phillips
    Guest

    Re: email validation?

    I use this UDF


    '-----------------------------------------------------------------
    Public Function ValidEmail(Adress As String) As Boolean
    '-----------------------------------------------------------------
    Dim oRegEx As Object
    Set oRegEx = CreateObject("VBScript.RegExp")
    With oRegEx
    .Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
    ' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
    ValidEmail = .Test(Adress)
    End With
    Set oRegEx = Nothing
    End Function


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "GoBobbyGo" <GoBobbyGo@discussions.microsoft.com> wrote in message
    news:80B60297-C7C5-44C7-9BD2-C11CA2484C99@microsoft.com...
    > I ended up just going with the function:
    >
    >

    =IF(OR(ISERROR(FIND("@",M14)),ISERROR(FIND(".",RIGHT(M14,LEN(M14)-FIND("@",M
    14))))),"invalid",IF(AND(FIND("@",M14)>1,ISERROR(FIND("@",RIGHT(M14,LEN(M14)
    -FIND("@",M14)))),FIND(".",RIGHT(M14,LEN(M14)-FIND("@",M14)))>1,FIND(".",RIG
    HT(M14,LEN(M14)-FIND("@",M14)))<LEN(M14)-FIND("@",M14)),"valid","invalid"))
    > "Chip Pearson" wrote:
    >
    > > This one is better than my previous reply:
    > >
    > > If Len(S) - Len(Replace(S, "@", "")) = 1 Then
    > > If S Like "?*@?*.?*" Then
    > > Debug.Print "OK"
    > > Else
    > > Debug.Print "Not ok"
    > > End If
    > > Else
    > > Debug.Print "not ok"
    > > End If
    > >
    > >
    > > "Chip Pearson" <chip@cpearson.com> wrote in message
    > > news:OEKBW5yYGHA.3740@TK2MSFTNGP03.phx.gbl...
    > > > There is no built-in way to do this. Try
    > > >
    > > > Dim S As String
    > > > S = "chip@cpearson.com"
    > > > If S Like "?*@?*.?*" Then
    > > > Debug.Print "OK"
    > > > Else
    > > > Debug.Print "Not ok"
    > > > End If
    > > >
    > > >
    > > >
    > > > --
    > > > Cordially,
    > > > Chip Pearson
    > > > Microsoft MVP - Excel
    > > > Pearson Software Consulting, LLC
    > > > www.cpearson.com
    > > >
    > > >
    > > > "GoBobbyGo" <GoBobbyGo@discussions.microsoft.com> wrote in
    > > > message
    > > > news:2321863F-4F63-4948-BE47-BCD80DE8A211@microsoft.com...
    > > >> is there a built-in function in Excel that tells whether a
    > > >> text string is a
    > > >> valid email? (I don't mean whether the address exists or not,
    > > >> but rather if
    > > >> it has exactly one "@" in it, then some letters, then a ".",
    > > >> some more
    > > >> letters, etc)
    > > >>
    > > >> I can build one, but before I do...
    > > >>
    > > >> ----------------
    > > >> This post is a suggestion for Microsoft, and Microsoft
    > > >> responds to the
    > > >> suggestions with the most votes. To vote for this suggestion,
    > > >> click the "I
    > > >> Agree" button in the message pane. If you do not see the
    > > >> button, follow this
    > > >> link to open the suggestion in the Microsoft Web-based
    > > >> Newsreader and then
    > > >> click "I Agree" in the message pane.
    > > >>
    > > >>

    http://www.microsoft.com/office/comm...lic.excel.misc
    > > >
    > > >

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1