software

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 quot;@quot; in it, then some letters, then a quot;.quot;, 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 quot;I
Agreequot; 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 quot;I Agreequot; in the message pane.

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

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

Dim S As String
S = quot;
If S Like quot;?*@?*.?*quot; Then
Debug.Print quot;OKquot;
Else
Debug.Print quot;Not okquot;
End If
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.comquot;GoBobbyGoquot; gt; wrote in
message
...
gt; is there a built-in function in Excel that tells whether a text
gt; string is a
gt; valid email? (I don't mean whether the address exists or not,
gt; but rather if
gt; it has exactly one quot;@quot; in it, then some letters, then a quot;.quot;,
gt; some more
gt; letters, etc)
gt;
gt; I can build one, but before I do...
gt;
gt; ----------------
gt; This post is a suggestion for Microsoft, and Microsoft responds
gt; to the
gt; suggestions with the most votes. To vote for this suggestion,
gt; click the quot;I
gt; Agreequot; button in the message pane. If you do not see the
gt; button, follow this
gt; link to open the suggestion in the Microsoft Web-based
gt; Newsreader and then
gt; click quot;I Agreequot; in the message pane.
gt;
gt; www.microsoft.com/office/comm...lic.excel.misc
This one is better than my previous reply:

If Len(S) - Len(Replace(S, quot;@quot;, quot;quot;)) = 1 Then
If S Like quot;?*@?*.?*quot; Then
Debug.Print quot;OKquot;
Else
Debug.Print quot;Not okquot;
End If
Else
Debug.Print quot;not okquot;
End Ifquot;Chip Pearsonquot; gt; wrote in message
...
gt; There is no built-in way to do this. Try
gt;
gt; Dim S As String
gt; S = quot;
gt; If S Like quot;?*@?*.?*quot; Then
gt; Debug.Print quot;OKquot;
gt; Else
gt; Debug.Print quot;Not okquot;
gt; End If
gt;
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt; quot;GoBobbyGoquot; gt; wrote in
gt; message
gt; ...
gt;gt; is there a built-in function in Excel that tells whether a
gt;gt; text string is a
gt;gt; valid email? (I don't mean whether the address exists or not,
gt;gt; but rather if
gt;gt; it has exactly one quot;@quot; in it, then some letters, then a quot;.quot;,
gt;gt; some more
gt;gt; letters, etc)
gt;gt;
gt;gt; I can build one, but before I do...
gt;gt;
gt;gt; ----------------
gt;gt; This post is a suggestion for Microsoft, and Microsoft
gt;gt; responds to the
gt;gt; suggestions with the most votes. To vote for this suggestion,
gt;gt; click the quot;I
gt;gt; Agreequot; button in the message pane. If you do not see the
gt;gt; button, follow this
gt;gt; link to open the suggestion in the Microsoft Web-based
gt;gt; Newsreader and then
gt;gt; click quot;I Agreequot; in the message pane.
gt;gt;
gt;gt; www.microsoft.com/office/comm...lic.excel.misc
gt;
gt;
I ended up just going with the function:

=IF(OR(ISERROR(FIND(quot;@quot;,M14)),ISERROR(FIND(quot;.quot;,RIG HT(M14,LEN(M14)-FIND(quot;@quot;,M14))))),quot;invalidquot;,IF(AND(FIND(quot;@quot;,M14)gt;1 ,ISERROR(FIND(quot;@quot;,RIGHT(M14,LEN(M14)-FIND(quot;@quot;,M14)))),FIND(quot;.quot;,RIGHT(M14,LEN(M14)-FIND(quot;@quot;,M14)))gt;1,FIND(quot;.quot;,RIGHT(M14,LEN(M14)-FIND(quot;@quot;,M14)))lt;LEN(M14)-FIND(quot;@quot;,M14)),quot;validquot;,quot;invalidquot;))
quot;Chip Pearsonquot; wrote:

gt; This one is better than my previous reply:
gt;
gt; If Len(S) - Len(Replace(S, quot;@quot;, quot;quot;)) = 1 Then
gt; If S Like quot;?*@?*.?*quot; Then
gt; Debug.Print quot;OKquot;
gt; Else
gt; Debug.Print quot;Not okquot;
gt; End If
gt; Else
gt; Debug.Print quot;not okquot;
gt; End If
gt;
gt;
gt; quot;Chip Pearsonquot; gt; wrote in message
gt; ...
gt; gt; There is no built-in way to do this. Try
gt; gt;
gt; gt; Dim S As String
gt; gt; S = quot;
gt; gt; If S Like quot;?*@?*.?*quot; Then
gt; gt; Debug.Print quot;OKquot;
gt; gt; Else
gt; gt; Debug.Print quot;Not okquot;
gt; gt; End If
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Cordially,
gt; gt; Chip Pearson
gt; gt; Microsoft MVP - Excel
gt; gt; Pearson Software Consulting, LLC
gt; gt; www.cpearson.com
gt; gt;
gt; gt;
gt; gt; quot;GoBobbyGoquot; gt; wrote in
gt; gt; message
gt; gt; ...
gt; gt;gt; is there a built-in function in Excel that tells whether a
gt; gt;gt; text string is a
gt; gt;gt; valid email? (I don't mean whether the address exists or not,
gt; gt;gt; but rather if
gt; gt;gt; it has exactly one quot;@quot; in it, then some letters, then a quot;.quot;,
gt; gt;gt; some more
gt; gt;gt; letters, etc)
gt; gt;gt;
gt; gt;gt; I can build one, but before I do...
gt; gt;gt;
gt; gt;gt; ----------------
gt; gt;gt; This post is a suggestion for Microsoft, and Microsoft
gt; gt;gt; responds to the
gt; gt;gt; suggestions with the most votes. To vote for this suggestion,
gt; gt;gt; click the quot;I
gt; gt;gt; Agreequot; button in the message pane. If you do not see the
gt; gt;gt; button, follow this
gt; gt;gt; link to open the suggestion in the Microsoft Web-based
gt; gt;gt; Newsreader and then
gt; gt;gt; click quot;I Agreequot; in the message pane.
gt; gt;gt;
gt; gt;gt; www.microsoft.com/office/comm...lic.excel.misc
gt; gt;
gt; gt;
gt;
gt;
gt;

I use this UDF'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject(quot;VBScript.RegExpquot;)
With oRegEx
.Pattern = quot;^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$quot;
' .Pattern = quot;^(\w \.)*(\w )@(\w \.) ([a-zA-Z]{2,4})$quot;
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;GoBobbyGoquot; gt; wrote in message
...
gt; I ended up just going with the function:
gt;
gt;
=IF(OR(ISERROR(FIND(quot;@quot;,M14)),ISERROR(FIND(quot;.quot;,RIG HT(M14,LEN(M14)-FIND(quot;@quot;,M
14))))),quot;invalidquot;,IF(AND(FIND(quot;@quot;,M14)gt;1,ISERROR(F IND(quot;@quot;,RIGHT(M14,LEN(M14)
-FIND(quot;@quot;,M14)))),FIND(quot;.quot;,RIGHT(M14,LEN(M14)-FIND(quot;@quot;,M14)))gt;1,FIND(quot;.quot;,RIG
HT(M14,LEN(M14)-FIND(quot;@quot;,M14)))lt;LEN(M14)-FIND(quot;@quot;,M14)),quot;validquot;,quot;invalidquot;))
gt; quot;Chip Pearsonquot; wrote:
gt;
gt; gt; This one is better than my previous reply:
gt; gt;
gt; gt; If Len(S) - Len(Replace(S, quot;@quot;, quot;quot;)) = 1 Then
gt; gt; If S Like quot;?*@?*.?*quot; Then
gt; gt; Debug.Print quot;OKquot;
gt; gt; Else
gt; gt; Debug.Print quot;Not okquot;
gt; gt; End If
gt; gt; Else
gt; gt; Debug.Print quot;not okquot;
gt; gt; End If
gt; gt;
gt; gt;
gt; gt; quot;Chip Pearsonquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; There is no built-in way to do this. Try
gt; gt; gt;
gt; gt; gt; Dim S As String
gt; gt; gt; S = quot;
gt; gt; gt; If S Like quot;?*@?*.?*quot; Then
gt; gt; gt; Debug.Print quot;OKquot;
gt; gt; gt; Else
gt; gt; gt; Debug.Print quot;Not okquot;
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Cordially,
gt; gt; gt; Chip Pearson
gt; gt; gt; Microsoft MVP - Excel
gt; gt; gt; Pearson Software Consulting, LLC
gt; gt; gt; www.cpearson.com
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;GoBobbyGoquot; gt; wrote in
gt; gt; gt; message
gt; gt; gt; ...
gt; gt; gt;gt; is there a built-in function in Excel that tells whether a
gt; gt; gt;gt; text string is a
gt; gt; gt;gt; valid email? (I don't mean whether the address exists or not,
gt; gt; gt;gt; but rather if
gt; gt; gt;gt; it has exactly one quot;@quot; in it, then some letters, then a quot;.quot;,
gt; gt; gt;gt; some more
gt; gt; gt;gt; letters, etc)
gt; gt; gt;gt;
gt; gt; gt;gt; I can build one, but before I do...
gt; gt; gt;gt;
gt; gt; gt;gt; ----------------
gt; gt; gt;gt; This post is a suggestion for Microsoft, and Microsoft
gt; gt; gt;gt; responds to the
gt; gt; gt;gt; suggestions with the most votes. To vote for this suggestion,
gt; gt; gt;gt; click the quot;I
gt; gt; gt;gt; Agreequot; button in the message pane. If you do not see the
gt; gt; gt;gt; button, follow this
gt; gt; gt;gt; link to open the suggestion in the Microsoft Web-based
gt; gt; gt;gt; Newsreader and then
gt; gt; gt;gt; click quot;I Agreequot; in the message pane.
gt; gt; gt;gt;
gt; gt; gt;gt;
www.microsoft.com/office/comm...lic.excel.misc
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;