Prev: Changing multiple hyperlinks
Next: automatic calculate at specific month
From: yakir on 24 May 2010 05:59
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..

thank you all very much..

From: Dave Peterson on 24 May 2010 07:55
A quick and dirty check would be to look for the @ character in the cell.

You could insert a new column adjacent to the column with the data and use:

=if(countif(a1,"*@*")=0,"Nope","Maybe")
Where A1 is the cell to inspect.

Drag it down as far as you need.

Sort by this column
Delete the Nopes
and start looking to see if you need better rules.

If you do, you may want to post back with more samples of what your data looks
like (in each cell).

yakir 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..
>
> thank you all very much..

--

Dave Peterson
From: Huber57 on 24 May 2010 08:44
You can look for these characters, as well:

()[]\;:,<>@

None are allowed in an email address before the @ sign. Once you find all
of the email addresses without an @ sign and remove them, you could use 'Text
to columns' to split the email address between the local part of the address
(anything before the @ sign) and the domain name (everything after the @
sign). Use the @ sign itself as the delimiter. Then search for the
characters mentioned above in the local part of the address using Dave's
formula.

HTH.

"Dave Peterson" wrote:

> A quick and dirty check would be to look for the @ character in the cell.
>
> You could insert a new column adjacent to the column with the data and use:
>
> =if(countif(a1,"*@*")=0,"Nope","Maybe")
> Where A1 is the cell to inspect.
>
> Drag it down as far as you need.
>
> Sort by this column
> Delete the Nopes
> and start looking to see if you need better rules.
>
> If you do, you may want to post back with more samples of what your data looks
> like (in each cell).
>
> yakir 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..
> >
> > thank you all very much..
>
> --
>
> Dave Peterson
> .
>
From: bala_vb on 24 May 2010 13:11

yakir;955615 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..
>
> thank you all very much..

take all the email adress to spreadsheet and save as macro enabled
.xlsm or .xlm and follow below steps. Please refer to attached
spreadsheet for your reference.

Copy the below code.
Open the Excel file in which you want to use the code.
Hit Alt+F11 to open the Visual Basic Editor (VBE) window.
From the menu, choose Insert-Module.
Paste the code into the code window at right.
Save the file and close the VBE

'created and edited by bala sesharao
Public Function IsValidEmail(sEmail As String) As String
'Checks if the given Email is valid
'If Email is valid: returns empty string
'If Email is invalid: returns description of error

Dim sReason As String
Dim n As Integer
n = Len(sEmail) - InStrRev(sEmail, ".")

If sEmail <> Trim(sEmail) Then
sReason = "Leading or trailing spaces"
ElseIf Len(sEmail) <= 7 Then '-- Is a(a)b.com a valid email address?
sReason = "Too short"
ElseIf sEmail Like "*[!0-9a-z@._+-]*" Then
'other characters than 0-9 a-z @ . _ + - are not allowed, e.g. !
$ & ` ' * / \ = ? ^ | # % { } ~
sReason = "Invalid character"
ElseIf Not sEmail Like "*.*" Then
sReason = "Missing the ."
ElseIf Not sEmail Like "*@*" Then
sReason = "Missing the @"
ElseIf sEmail Like "*@*@*" Then
sReason = "Too many @"
ElseIf sEmail Like "[@.]*" Or sEmail Like "*[@.]" _
Or sEmail Like "*..*" Or Not sEmail Like "?*@?*.*?" Then
sReason = "Invalid format"
ElseIf n > 4 Then
'Check if suffix is between 2 (.tw etc) and 4 characters (.info
etc)
sReason = "Suffix too long"
ElseIf n < 2 Then
sReason = "Suffix too short"
Else
'If no problems, sReason is empty
sReason = "Valid Email Address"
End If

IsValidEmail = sReason

End Function

.all the best


+-------------------------------------------------------------------+
|Filename: Email Validation.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=152|
+-------------------------------------------------------------------+



--
bala_vb
 | 
Pages: 1
Prev: Changing multiple hyperlinks
Next: automatic calculate at specific month