Excel to check for regular expression?

user · External Since: Jul 02, 2007 Posts: 2

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

Flick Olmsford · External Since: Mar 08, 2007 Posts: 7

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

"user" wrote:

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

Harlan Grove · External Since: Jun 29, 2007 Posts: 107

"Bob Phillips" <bob.... DeleteThis @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])

user · External Since: Jul 02, 2007 Posts: 2

On Jul 3, 2:13 am, Harlan Grove <hrln....DeleteThis@aol.com> wrote:
> "Bob Phillips" <bob.....DeleteThis@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])

So How do we start using it in Excel? Thanks

Bob Phillips · External Since: Mar 01, 2007 Posts: 967

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)

"user" <mylinuxjourney.TakeThisOut@gmail.com> wrote in message
news:1183389676.478316.284990@d30g2000prg.googlegroups.com...
> 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
>

Bob Phillips · External Since: Mar 01, 2007 Posts: 967

as I showed.

--
HTH

Bob

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

"user" <mylinuxjourney.TakeThisOut@gmail.com> wrote in message
news:1183414286.769076.269750@m37g2000prh.googlegroups.com...
> On Jul 3, 2:13 am, Harlan Grove <hrln....TakeThisOut@aol.com> wrote:
>> "Bob Phillips" <bob.....TakeThisOut@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])
>
> So How do we start using it in Excel? Thanks
>

ze tolas · External Since: Nov 05, 2009 Posts: 1

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

Bob Phillips wrote:

This function will test that an passed emaiul address is properly
02-Jul-07

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)

"user" <mylinuxjourney.DeleteThis@gmail.com> wrote in message
news:1183389676.478316.284990@d30g2000prg.googlegroups.com...

Previous Posts In This Thread:

On segunda-feira, 2 de Julho de 2007 11:21
user wrote:

Excel to check for regular expression?
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

On segunda-feira, 2 de Julho de 2007 11:56
FlickOlmsfor wrote:

By valid, do you mean that the email is in proper syntax or that it is an
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

"user" wrote:

On segunda-feira, 2 de Julho de 2007 12:28
Bob Phillips wrote:

This function will test that an passed emaiul address is properly
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)

"user" <mylinuxjourney.DeleteThis@gmail.com> wrote in message
news:1183389676.478316.284990@d30g2000prg.googlegroups.com...

On segunda-feira, 2 de Julho de 2007 14:13
Harlan Grove wrote:

Re: Excel to check for regular expression?
"Bob Phillips" <bob.....DeleteThis@somewhere.com> wrote...

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

On segunda-feira, 2 de Julho de 2007 18:11
user wrote:

Re: Excel to check for regular expression?
So How do we start using it in Excel? Thanks

On ter?a-feira, 3 de Julho de 2007 3:39
Bob Phillips wrote:

as I showed.
as I showed.

--
HTH

Bob

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

EggHeadCafe - Software Developer Portal of Choice
..NET Abstract Factory
http://www.eggheadcafe.com/tutorials/aspnet/43ecdbdc-7598-410e-93ed-0f...cb15b39

Bernie Deitrick · External Since: Apr 02, 2004 Posts: 1772

Copy the code into a regular codemodule in your workbook:

Copy the code
Select the workbook
Press Alt-F11 to open the Visual Basic Editor
Choose Insert | Module
Press Ctrl-V to paste the code

Use it like

=ValidEmail(A2)

where A2 has an email address string.

Or, to use it from VBA, select the cell and run this macro

Sub IsItGood()
If ValidEmail(ActiveCell.Value) Then
MsgBox "That one is good"
Else
MsgBox "Not so good"
End If
End Sub

HTH,
Bernie
MS Excel MVP

"ze tolas" wrote in message news:2009115152534ddiniz@ual.pt...
> 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
>
>
>
> Bob Phillips wrote:
>
> This function will test that an passed emaiul address is properly
> 02-Jul-07
>
> 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)
>
> "user" <mylinuxjourney RemoveThis @gmail.com> wrote in message
> news:1183389676.478316.284990@d30g2000prg.googlegroups.com...
>
> Previous Posts In This Thread:
>
> On segunda-feira, 2 de Julho de 2007 11:21
> user wrote:
>
> Excel to check for regular expression?
> 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
>
> On segunda-feira, 2 de Julho de 2007 11:56
> FlickOlmsfor wrote:
>
> By valid, do you mean that the email is in proper syntax or that it is an
> 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
>
>
>
> "user" wrote:
>
> On segunda-feira, 2 de Julho de 2007 12:28
> Bob Phillips wrote:
>
> This function will test that an passed emaiul address is properly
> 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)
>
> "user" <mylinuxjourney RemoveThis @gmail.com> wrote in message
> news:1183389676.478316.284990@d30g2000prg.googlegroups.com...
>
> On segunda-feira, 2 de Julho de 2007 14:13
> Harlan Grove wrote:
>
> Re: Excel to check for regular expression?
> "Bob Phillips" <bob.... RemoveThis @somewhere.com> wrote...
>
> 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])
>
> On segunda-feira, 2 de Julho de 2007 18:11
> user wrote:
>
> Re: Excel to check for regular expression?
> So How do we start using it in Excel? Thanks
>
> On ter?a-feira, 3 de Julho de 2007 3:39
> Bob Phillips wrote:
>
> as I showed.
> as I showed.
>
> --
> HTH
>
> Bob
>
> (there is no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> EggHeadCafe - Software Developer Portal of Choice
> .NET Abstract Factory
> http://www.eggheadcafe.com/tutorials/aspnet/43ecdbdc-7598-410e-93ed-0f...cb15b39