please help.. highly important... [Programming]

Prev: Using Trim in a Userform
Next: Try this

From: yakir on 24 May 2010 06:05

i have a excel .CSV file that Gmail generated for me with my contacts in..
what i want to do is function that go over all the contacts emails one by
one and deletes all missformated addresses that cannot be use to send email
for them.. somthing like :

if (the cell's text is not an valid email address)
{
delete the cell ;
}

to be honest i dont know much about excel so i need somthing from
scratch like how to put the function in the file and that..

thank you all very much..

From: Jim Cone on 24 May 2010 06:38

Also posted in public.excel.misc

"yakir" <yakir(a)discussions.microsoft.com>
wrote in message news:C4250A8E-A54B-44A3-8DE8-CDF9FC2C0ED5(a)microsoft.com...
i have a excel .CSV file that Gmail generated for me with my contacts in..
what i want to do is function that go over all the contacts emails one by
one and deletes all missformated addresses that cannot be use to send email
for them.. somthing like :

if (the cell's text is not an valid email address)
{
delete the cell ;
}

to be honest i dont know much about excel so i need somthing from
scratch like how to put the function in the file and that..

thank you all very much..

From: Chip Pearson on 24 May 2010 08:21

In VBA insert a new module. Then go to the Tools menu, choose
References, and scroll down to and check "Microsoft VBScript Regular
Expressions 5.5". Then, use code like the following

Sub AAA()
Dim LastRow As Long
Dim RowNdx As Long
Dim TopRow As Long
Dim WS As Worksheet
Dim Pattern As String
Dim RegEx As RegExp
Const ADDR_COL = "A" '<<<< CHANGE
TopRow = 1 '<<<< CHANGE
Pattern = "(^[a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}" & _
"\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})$"
Set RegEx = New RegExp
RegEx.Pattern = Pattern
Set WS = ActiveSheet
With WS
LastRow = .Cells(.Rows.Count, ADDR_COL).End(xlUp).Row
For RowNdx = LastRow To TopRow Step -1
If RegEx.Test(.Cells(RowNdx, ADDR_COL).Text) = False Then
.Rows(RowNdx).Delete
End If
Next RowNdx
End With
End Sub

Change the lines marked with <<<< to the correct values. ADDR_COL
should be the column letter of the column containing the email
addresses to test. TopRow should be the row number of the top of the
range of values to test. The code begins testing the addresses on the
row calculated as LastRow and moves upwards until it reaches TopRow,
at which point it quits. For each row between LastRow and TopRow
(inclusive), if an invalid email address is found in ADDR_COL, that
row is deleted.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

On Mon, 24 May 2010 03:05:01 -0700, yakir
<yakir(a)discussions.microsoft.com> wrote:

>i have a excel .CSV file that Gmail generated for me with my contacts in..
>what i want to do is function that go over all the contacts emails one by
>one and deletes all missformated addresses that cannot be use to send email
>for them.. somthing like :
>
>if (the cell's text is not an valid email address)
>{
>delete the cell ;
>}
>
>to be honest i dont know much about excel so i need somthing from
>scratch like how to put the function in the file and that..
>
>thank you all very much..

From: Ron Rosenfeld on 24 May 2010 22:25

On Mon, 24 May 2010 07:21:17 -0500, Chip Pearson <chip(a)cpearson.com> wrote:

>In VBA insert a new module. Then go to the Tools menu, choose
>References, and scroll down to and check "Microsoft VBScript Regular
>Expressions 5.5". Then, use code like the following
>
>Sub AAA()
>Dim LastRow As Long
>Dim RowNdx As Long
>Dim TopRow As Long
>Dim WS As Worksheet
>Dim Pattern As String
>Dim RegEx As RegExp
>Const ADDR_COL = "A" '<<<< CHANGE
>TopRow = 1 '<<<< CHANGE
>Pattern = "(^[a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}" & _
> "\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})$"
>Set RegEx = New RegExp
>RegEx.Pattern = Pattern
>Set WS = ActiveSheet
>With WS
> LastRow = .Cells(.Rows.Count, ADDR_COL).End(xlUp).Row
> For RowNdx = LastRow To TopRow Step -1
> If RegEx.Test(.Cells(RowNdx, ADDR_COL).Text) = False Then
> .Rows(RowNdx).Delete
> End If
> Next RowNdx
>End With
>End Sub
>
>
>Change the lines marked with <<<< to the correct values. ADDR_COL
>should be the column letter of the column containing the email
>addresses to test. TopRow should be the row number of the top of the
>range of values to test. The code begins testing the addresses on the
>row calculated as LastRow and moves upwards until it reaches TopRow,
>at which point it quits. For each row between LastRow and TopRow
>(inclusive), if an invalid email address is found in ADDR_COL, that
>row is deleted.
>
>Cordially,
>Chip Pearson
>Microsoft Most Valuable Professional,
> Excel, 1998 - 2010
>Pearson Software Consulting, LLC
>www.cpearson.com
>
>
>
>

It's tough to devise a regex that can comply with the relevant internet
standards. I think the current one is RFC5322

Your regex excludes some email addresses which I believe are valid, and
includes some which are invalid.

For example, your regex excludes any TLD's that have more than four characters.
But the current generic domain list includes .museum & .travel, and there will
likely be more, longer lettered TLD's in the future.

It also excludes some characters that are valid in email addresses, at least in
the local part:

! # $ % & ' * + - / = ? ^ _ ` { | } ~

It allows consecutive dots in the local part of the address, as well as
allowing them at the start or end of the local part of the address -- and that
is not valid.

Since the OP is looking for malformed email addresses, I'd be concerned that
your regex would eliminate some valid one's, and include some invalid one's.

It can be pretty tough to devise something that does not have false positives
or negatives. If IP addresses don't need to be included, I'd suggest something
(admittedly incomplete) like (with the case insensitive option (ignorecase)
equal to true):

"^[-\w.%+']+@[A-Z0-9.-]+\.(?:aero|asia|biz|cat|com|coop|edu|gov|info|int|" _
& "jobs|mil|mobi|museum|name|net|org|pro|tel|travel|[A-Z]{2})$"

which will allow any two character domain name (to allow for the country codes)
as well as the currently used specific list of generic top-level domains.

--ron

From: Ron Rosenfeld on 25 May 2010 05:55

| Next | Last
Pages: 1 2
Prev: Using Trim in a Userform
Next: Try this