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

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.
--
Thanks,
CTaylor

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

HTH
Jason
Atlanta, GA

>-----Original Message-----
>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.
>--
>Thanks,
>CTaylor
>.
>

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)


"RmeAV8tor" <[email protected](doNOTspam)> wrote in message
news:[email protected]...
> 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.
> --
> Thanks,
> CTaylor

Oops, it should be $A$1:$A$100 in the formula.

Jaosn

>-----Original Message-----
>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
>
>HTH
>Jason
>Atlanta, GA
>
>>-----Original Message-----
>>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.
>>--
>>Thanks,
>>CTaylor
>>.
>>
>.
>

Bob,

Where do I cut and paste your function to?


"Bob Phillips" wrote:

> 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)
>
>
> "RmeAV8tor" <[email protected](doNOTspam)> wrote in message
> news:[email protected]...
> > 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.
> > --
> > Thanks,
> > CTaylor
>
>
>

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.

"Jason Morin" wrote:

> Oops, it should be $A$1:$A$100 in the formula.
>
> Jaosn
>
> >-----Original Message-----
> >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
> >
> >HTH
> >Jason
> >Atlanta, GA
> >
> >>-----Original Message-----
> >>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.
> >>--
> >>Thanks,
> >>CTaylor
> >>.
> >>
> >.
> >
>