Varifying e-mail address

[Gav]

Hi,

Any thoughts how i would verify that a cell only contains
the following chars:
[a-z],[0-9],.,@,_,-

I've got a list of e-mail addresses in Excel & would like
to make sure they only contain these chars.

I've started with this formula:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF(CLEAN
(C2)<>C2, "Contains non-printing characters", "ok"))

Where the column C has the e-mail address.

Thanks,
Gav

 

[Guest]

One way might be:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF(CLEAN(C2)<>C2,
"Contains non-printing
characters",IF(OR(NOT(ISERR(FIND({"!","#","$","%","^"},C2)))),"Contains
invalid characters","ok")))

You will need to replace my sample set of invalid characters with the ones
you wish to trap, or alternately put them in a separate range and refer to
that range in your formula. You can trap your spaces in the last bit too and
omit the first IF statement to shorten the formula.

HTH,
Ryan

 

[Bob Phillips]

A regular expressions UDF would be best for this, such as

Public Function ValidateEmail(MailAddress As String) As Boolean
Dim oRegExp As Object
Set oRegExp = CreateObject("VBScript.RegExp")
With oRegExp
.Pattern = "^\w+((-\w+)|(\.\w+))*\@\w+((\.|-)\w+)*\.\w+$"
ValidateEmail = .Test(MailAddress)
End With
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

One way might be:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF(CLEAN(C2)<>C2,
"Contains non-printing
characters",IF(OR(NOT(ISERR(FIND({"!","#","$","%","^"},C2)))),"Contains
invalid characters","ok")))

You will need to replace my sample set of invalid characters with the ones
you wish to trap, or alternately put them in a separate range and refer to
that range in your formula. You can trap your spaces in the last bit too and
omit the first IF statement to shorten the formula.

HTH,
Ryan

Hi,

Any thoughts how i would verify that a cell only contains
the following chars:
[a-z],[0-9],.,@,_,-

I've got a list of e-mail addresses in Excel & would like
to make sure they only contain these chars.

I've started with this formula:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF(CLEAN
(C2)<>C2, "Contains non-printing characters", "ok"))

Where the column C has the e-mail address.

Thanks,
Gav

 

[Gavin]

That sounds like just the function i need.
How do i set up the worksheet to call it?
I've got all the e-mail addresses in cells C2:C1830.
The formua will be in the equivelent cells in col E.

Regards,
Gavin

-----Original Message-----
A regular expressions UDF would be best for this, such as

Public Function ValidateEmail(MailAddress As String) As Boolean
Dim oRegExp As Object
Set oRegExp = CreateObject("VBScript.RegExp")
With oRegExp
.Pattern = "^\w+((-\w+)|(\.\w+))*\@\w+((\.|-)\w+) *\.\w+$"
ValidateEmail = .Test(MailAddress)
End With
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

One way might be:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF

"Contains non-printing
characters",IF(OR(NOT(ISERR(FIND ({"!","#","$","%","^"},C2)))),"Contains
invalid characters","ok")))

You will need to replace my sample set of invalid characters with the ones
you wish to trap, or alternately put them in a separate range and refer to
that range in your formula. You can trap your spaces in

the last bit too
and

omit the first IF statement to shorten the formula.

HTH,
Ryan

Hi,

Any thoughts how i would verify that a cell only contains
the following chars:
[a-z],[0-9],.,@,_,-

I've got a list of e-mail addresses in Excel & would like
to make sure they only contain these chars.

I've started with this formula:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF (CLEAN
(C2)<>C2, "Contains non-printing characters", "ok"))

Where the column C has the e-mail address.

Thanks,
Gav

.

 

[Bob Phillips]

Gavin,

In E2, =ValidateEmail(C2), and copy down

It will the show TRUE or FALSE

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

That sounds like just the function i need.
How do i set up the worksheet to call it?
I've got all the e-mail addresses in cells C2:C1830.
The formua will be in the equivelent cells in col E.

Regards,
Gavin

-----Original Message-----
A regular expressions UDF would be best for this, such as

Public Function ValidateEmail(MailAddress As String) As Boolean
Dim oRegExp As Object
Set oRegExp = CreateObject("VBScript.RegExp")
With oRegExp
.Pattern = "^\w+((-\w+)|(\.\w+))*\@\w+((\.|-)\w+) *\.\w+$"
ValidateEmail = .Test(MailAddress)
End With
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

One way might be:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF

"Contains non-printing
characters",IF(OR(NOT(ISERR(FIND ({"!","#","$","%","^"},C2)))),"Contains
invalid characters","ok")))

You will need to replace my sample set of invalid characters with the ones
you wish to trap, or alternately put them in a separate range and refer to
that range in your formula. You can trap your spaces in

the last bit too
and

omit the first IF statement to shorten the formula.

HTH,
Ryan

:

Hi,

Any thoughts how i would verify that a cell only contains
the following chars:
[a-z],[0-9],.,@,_,-

I've got a list of e-mail addresses in Excel & would like
to make sure they only contain these chars.

I've started with this formula:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF (CLEAN
(C2)<>C2, "Contains non-printing characters", "ok"))

Where the column C has the e-mail address.

Thanks,
Gav

.

 

[Gavin]

That's what I thought, but it's coming up with "#NAME?"
(w/o quotes).

-----Original Message-----
Gavin,

In E2, =ValidateEmail(C2), and copy down

It will the show TRUE or FALSE

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

That sounds like just the function i need.
How do i set up the worksheet to call it?
I've got all the e-mail addresses in cells C2:C1830.
The formua will be in the equivelent cells in col E.

Regards,
Gavin

-----Original Message-----
A regular expressions UDF would be best for this, such as

Public Function ValidateEmail(MailAddress As String) As Boolean
Dim oRegExp As Object
Set oRegExp = CreateObject("VBScript.RegExp")
With oRegExp
.Pattern = "^\w+((-\w+)|(\.\w+))*\@\w+((\.|-)

\w+)
*\.\w+$"

ValidateEmail = .Test(MailAddress)
End With
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ryan Poth" <[email protected]> wrote

in
message

news:E10834D8-0126-49D0-9553- (e-mail address removed)...
One way might be:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF

"Contains non-printing
characters",IF(OR(NOT(ISERR(FIND ({"!","#","$","%","^"},C2)))),"Contains
invalid characters","ok")))

You will need to replace my sample set of invalid characters with the ones
you wish to trap, or alternately put them in a

separate
range and refer to

that range in your formula. You can trap your spaces

in
the last bit too

and
omit the first IF statement to shorten the formula.

HTH,
Ryan

:

Hi,

Any thoughts how i would verify that a cell only contains
the following chars:
[a-z],[0-9],.,@,_,-

I've got a list of e-mail addresses in Excel &

would
like

to make sure they only contain these chars.

I've started with this formula:

=IF(TRIM(C2)<>C2,"Contains spaces at start or

end",IF
(CLEAN

(C2)<>C2, "Contains non-printing characters", "ok"))

Where the column C has the e-mail address.

Thanks,
Gav




.

.

 

[Frank Kabel]

Hi
where have you stored the macro? It has to go in a standard module of
your workbook

--
Regards
Frank Kabel
Frankfurt, Germany

That's what I thought, but it's coming up with "#NAME?"
(w/o quotes).

-----Original Message-----
Gavin,

In E2, =ValidateEmail(C2), and copy down

It will the show TRUE or FALSE

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

That sounds like just the function i need.
How do i set up the worksheet to call it?
I've got all the e-mail addresses in cells C2:C1830.
The formua will be in the equivelent cells in col E.

Regards,
Gavin


-----Original Message-----
A regular expressions UDF would be best for this, such as

Public Function ValidateEmail(MailAddress As String) As Boolean
Dim oRegExp As Object
Set oRegExp = CreateObject("VBScript.RegExp")
With oRegExp
.Pattern = "^\w+((-\w+)|(\.\w+))*\@\w+((\.|-) \w+) *\.\w+$"
ValidateEmail = .Test(MailAddress)
End With
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

news:E10834D8-0126-49D0-9553- (e-mail address removed)...
One way might be:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF
(CLEAN(C2)<>C2, "Contains non-printing
characters",IF(OR(NOT(ISERR(FIND
({"!","#","$","%","^"},C2)))),"Contains
invalid characters","ok")))

You will need to replace my sample set of invalid characters with
the ones you wish to trap, or alternately put them in a separate
range and refer to that range in your formula. You can trap your
spaces in
the last bit too
and
omit the first IF statement to shorten the formula.

HTH,
Ryan

:

Hi,

Any thoughts how i would verify that a cell only contains
the following chars:
[a-z],[0-9],.,@,_,-

I've got a list of e-mail addresses in Excel & would like
to make sure they only contain these chars.

I've started with this formula:

=IF(TRIM(C2)<>C2,"Contains spaces at start or end",IF (CLEAN
(C2)<>C2, "Contains non-printing characters", "ok"))

Where the column C has the e-mail address.

Thanks,
Gav




.

.