Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
hello everybody.. question about .csv file...
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.. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
hello everybody.. question about .csv file...
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
hello everybody.. question about .csv file...
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 . |
#4
|
|||
|
|||
Quote:
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 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
__________________
Thanks Bala |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question on saving a file. | Excel Discussion (Misc queries) | |||
Excel File Question | Excel Discussion (Misc queries) | |||
Text File Question | Excel Worksheet Functions | |||
File name question | Excel Discussion (Misc queries) | |||
file format question | Excel Discussion (Misc queries) |