Excel - Excel to check for regular expression?

Asked By user

02-Jul-07 11:21 AM

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

02-Jul-07 11:56 AM

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

02-Jul-07 12:28 PM

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


--
HTH

Bob

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

02-Jul-07 02:13 PM

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.

...
...

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])

02-Jul-07 06:11 PM

So How do we start using it in Excel? Thanks

03-Jul-07 03:39 AM

as I showed.

--
HTH

Bob

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

05-Nov-09 03:25 PM

Hey man, I think that's just it!!! my only problem now is I don't know how to tell the collum or the cell to use it... could you help?

thanks