Excel to check for regular expression?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

user - 02 Jul 2007 16:21 GMT

Hi,

How do you use Regular expression in excel? For eg: Check a column of
data to see whether are all of them valid email addresses?

Please advise

THanks

Reply to this Message

Flick Olmsford - 02 Jul 2007 16:56 GMT

By valid, do you mean that the email is in proper syntax or that it is an
actual email address.

Text formulas can be used for proper syntax. Perhaps a Visual Basic
proceedure.

I doubt you can check that the email address actually exists, as many
companies and ISPs will simply disregard and drop mail to invalid email
addresses to eliminate spam

> Hi,
>
[quoted text clipped - 4 lines]
>
> THanks

Reply to this Message

Bob Phillips - 02 Jul 2007 17:28 GMT

This function will test that an passed emaiul address is properly
constructed

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

Signature

HTH

Bob

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

> Hi,
>
[quoted text clipped - 4 lines]
>
> THanks

Reply to this Message

Harlan Grove - 02 Jul 2007 19:13 GMT

"Bob Phillips" <bob....@somewhere.com> wrote...

>This function will test that an passed emaiul address is properly
>constructed

It doesn't handle every valid e-mail address. A lot more characters
than Latin letters, decimal numerals and underscores are allowed, at
least when the mailbox is inside double quotes.

>Public Function ValidEmail(Adress As String) As Boolean

...

> .Pattern = _
>"^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"

...

FWIW, this would happily match

.@-.-- and -@-.--

which aren't valid e-mail addresses. Also, {1,} isn't as efficient
either in terms of typing or processing as +.

The very end of the e-mail address is the top-level domain, which
should only contain Latin letters but *could* span up to 6 letters
(currently as of posting date, e.g., .museum and .travel), so you want
to end the regexp with [A-Za-z]{2,6}$. However, literal IP addresses
are also supported, so the entire part to the right of the @ could be

((2([0-4]\d|5[0-5])|1\d{2}|[1-9]\d|[1-9])\.){3}(2([0-4]\d|5[0-5])|
1\d{2}|[1-9]\d|[1-9])

Reply to this Message

user - 02 Jul 2007 23:11 GMT

> "Bob Phillips" <bob....@somewhere.com> wrote...
> >This function will test that an passed emaiul address is properly
[quoted text clipped - 26 lines]
> ((2([0-4]\d|5[0-5])|1\d{2}|[1-9]\d|[1-9])\.){3}(2([0-4]\d|5[0-5])|
> 1\d{2}|[1-9]\d|[1-9])

So How do we start using it in Excel? Thanks

Reply to this Message

Bob Phillips - 03 Jul 2007 08:39 GMT

as I showed.

Signature

HTH

Bob

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

>> "Bob Phillips" <bob....@somewhere.com> wrote...
>> >This function will test that an passed emaiul address is properly
[quoted text clipped - 28 lines]
>
> So How do we start using it in Excel? Thanks

Reply to this Message