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

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
>
>

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
> >
> >
>
>
>

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
> > >
> > >
> >
> >
> >