L
ljCharlie
Is there a formula where I can use to check if the email address is
valid or not? Help is much appreciated.
valid or not? Help is much appreciated.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
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
=ValidEmail([email protected])
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
'-----------------------------------------------------------------
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
=ValidEmail([email protected])
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
R
Ron Rosenfeld
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
L
ljCharlie
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.
quotation, apostrophe, period, colon, and semicolon at the end or in
some part of the email address where it's not suppose to be.
L
ljCharlie
Bob said: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
=ValidEmail([email protected])
How do I modify the code so that I can specify an email column
R
Ron Rosenfeld
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
B
Bob Phillips
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)
column of cells?
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
ljCharlie said:Bob said: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
=ValidEmail([email protected])
How do I modify the code so that I can specify an email column?