how do I format a cell to validate for an e-mail address ( @ .com.

R

RmeAV8tor

how do I format a cell to validate for an e-mail address ( @ .com, net, gov,
etc)

I want to make sure the user is entering a valid type of e-mail address.
 
J

Jason Morin

There are a lot of domain extensions if you consider
international. With the list of extensions in A1:A100,
select the target cell, go to Data > Validation,
under "Allow" select "Custom" and put:

=SUMPRODUCT(--NOT(ISNA(MATCH("*@*"&$A$1:$A$5&"*",D1,0))))
0
Click to expand...

HTH
Jason
Atlanta, GA
 
B

Bob Phillips

Here is a function that you can use

'-----------------------------------------------------------------
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}$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

RmeAV8tor

Bob,

Where do I cut and paste your function to?


Bob Phillips said:
Here is a function that you can use

'-----------------------------------------------------------------
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}$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
R

RmeAV8tor

I copied the formula; but I get an invalid error message no matter what I put
in the field. No matter right or wrong format, I always get an error.

This is the validation formula as I entered it:
=SUMPRODUCT(--NOT(ISNA(MATCH("*@*"&$AA$2:$AA$4&"*",D1,0))))>0
where I put com, net, and gov in cells AA2 : AA4. I put both .com and (no
dot) com; and still get errors.

Can you tell what I am doing wrong? I deleted the >0 at the end and just
ended the formula as ))))0; and still got an error.
 
Top