Is there a formula where I can use to check if the email address is
valid or not? Help is much appreciated.




--
ljCharlie

Add this VBA function


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


and use like

)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"ljCharlie" wrote in message
...

Is there a formula where I can use to check if the email address is
valid or not? Help is much appreciated.




--
ljCharlie

On Mon, 19 Feb 2007 21:38:26 +0000, ljCharlie
wrote:


Is there a formula where I can use to check if the email address is
valid or not? Help is much appreciated.

Well, you won't really know if an email address is valid until you try to send
email to that address.

And if you accept all of the variations allowed in the official RFC 822
http://www.ietf.org/rfc/rfc0822.txt standard, your task would be quite
complex, and your email client probably wouldn't be able to handle some of the
valid email addresses.

Do you want to limit to a list of certain top level domains? (e.g. .nu .ru .
il .com .org .net .edu .museum) or do you want to allow any series of 2 to 3 or
4 or 6 characters?

Do you want to limit to only English characters? Or can you allow non-English
characters?

Will the email address to be checked be the only item in the text string? Or
might it be embedded in a longer string?

And so forth.

So you'll need to define how you want a valid email address to appear. Once
you've done that, you can use a Regular Expression using either a UDF or, more
simply, a function in Longre's free morefunc.xll add-in http://xcell05.free.fr/


--ron

Many thanks for the help. By valid I mean an email address should have a
quotation, apostrophe, period, colon, and semicolon at the end or in
some part of the email address where it's not suppose to be.




--
ljCharlie

Bob Phillips Wrote:
Add this VBA function


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


and use like

)



How do I modify the code so that I can specify an email column?




--
ljCharlie

On Tue, 20 Feb 2007 13:40:17 +0000, ljCharlie
wrote:


Many thanks for the help. By valid I mean an email address should have a
quotation, apostrophe, period, colon, and semicolon at the end or in
some part of the email address where it's not suppose to be.

What you write doesn't make sense to me. Even if you were describing an invalid
rather than a valid address, you need to define where you think these
particular characters are not supposed to be, other than at the end.


But try this: Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use this formula which will return TRUE or FALSE depending on whether an
email address might be valid.

=REGEX.COMP(A1,"(^|\b)[\w._%-]+@[\w.-]+\.[A-Za-z]{2,4}(\b|$)")

A valid email address is defined as:

Starts either at the beginning of the text string or after a word break

The part before the "@" consists of any combination of English letters, digits,
dot, underscore, percent sign or dash.

@

The part after the "@" but before the terminal dot consists of any combination
of English letters, digits, dot or dash.

.. (dot)

The domain is any combination of English letters that is two to four characters
in length.

If you want something more comprehensive, you'll need to post back.
--ron

This a UDF as given. Do you mean that you want to validate a single cell, a
column of cells?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"ljCharlie" wrote in message
...

Bob Phillips Wrote:
Add this VBA function


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


and use like

)



How do I modify the code so that I can specify an email column?




--
ljCharlie