Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have a large (~10,000) list of former clients in an excel file that was
created when I pulled a report from my third party software. I've contracted with a newsletter company to help stay in touch with them. The newsletter company charges by entry so I need to delete any rows that aren't email addresses. I've already found the marco that deletes duplicate entries, and I can easily sort to get rid of entries with no email address, now I need some way to delete entries that have something other than an email address in the "email address" column. The answer could be several things: a helper column that tests for wildcard@wildcard, or a macro that tests and deletes rows. I haven't been able to get anything to work yet. Any help is appreciated. Regards, |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Here is a function that tests it
'----------------------------------------------------------------- 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}$" ' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$" ValidEmail = .Test(Adress) End With Set oRegEx = Nothing End Function just add =ValidEmail(A1) in B1 say. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sb1920alk" wrote in message ... I have a large (~10,000) list of former clients in an excel file that was created when I pulled a report from my third party software. I've contracted with a newsletter company to help stay in touch with them. The newsletter company charges by entry so I need to delete any rows that aren't email addresses. I've already found the marco that deletes duplicate entries, and I can easily sort to get rid of entries with no email address, now I need some way to delete entries that have something other than an email address in the "email address" column. The answer could be several things: a helper column that tests for wildcard@wildcard, or a macro that tests and deletes rows. I haven't been able to get anything to work yet. Any help is appreciated. Regards, |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Ok, I'm lost. I copied your code, right-clicked my tab name and then pasted it.
What do you mean, "just add =ValidEmail(A1) in B1 say." Where do I add it? Thanks, "Bob Phillips" wrote: Here is a function that tests it '----------------------------------------------------------------- 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}$" ' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$" ValidEmail = .Test(Adress) End With Set oRegEx = Nothing End Function just add =ValidEmail(A1) in B1 say. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sb1920alk" wrote in message ... I have a large (~10,000) list of former clients in an excel file that was created when I pulled a report from my third party software. I've contracted with a newsletter company to help stay in touch with them. The newsletter company charges by entry so I need to delete any rows that aren't email addresses. I've already found the marco that deletes duplicate entries, and I can easily sort to get rid of entries with no email address, now I need some way to delete entries that have something other than an email address in the "email address" column. The answer could be several things: a helper column that tests for wildcard@wildcard, or a macro that tests and deletes rows. I haven't been able to get anything to work yet. Any help is appreciated. Regards, |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You shouldn't store it in the sheet code module, but in a standard code
module, it is a UDF. You use it in the worksheet by, assuming that the address are in column A, in B1 put =ValidEmail(A1) and copy down, then filter column B by False and delete the visible rows. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sb1920alk" wrote in message ... Ok, I'm lost. I copied your code, right-clicked my tab name and then pasted it. What do you mean, "just add =ValidEmail(A1) in B1 say." Where do I add it? Thanks, "Bob Phillips" wrote: Here is a function that tests it '----------------------------------------------------------------- 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}$" ' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$" ValidEmail = .Test(Adress) End With Set oRegEx = Nothing End Function just add =ValidEmail(A1) in B1 say. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sb1920alk" wrote in message ... I have a large (~10,000) list of former clients in an excel file that was created when I pulled a report from my third party software. I've contracted with a newsletter company to help stay in touch with them. The newsletter company charges by entry so I need to delete any rows that aren't addresses. I've already found the marco that deletes duplicate entries, and I can easily sort to get rid of entries with no email address, now I need some way to delete entries that have something other than an email address in the "email address" column. The answer could be several things: a helper column that tests for wildcard@wildcard, or a macro that tests and deletes rows. I haven't been able to get anything to work yet. Any help is appreciated. Regards, |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
That did it. Thanks
"Bob Phillips" wrote: You shouldn't store it in the sheet code module, but in a standard code module, it is a UDF. You use it in the worksheet by, assuming that the address are in column A, in B1 put =ValidEmail(A1) and copy down, then filter column B by False and delete the visible rows. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sb1920alk" wrote in message ... Ok, I'm lost. I copied your code, right-clicked my tab name and then pasted it. What do you mean, "just add =ValidEmail(A1) in B1 say." Where do I add it? Thanks, "Bob Phillips" wrote: Here is a function that tests it '----------------------------------------------------------------- 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}$" ' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$" ValidEmail = .Test(Adress) End With Set oRegEx = Nothing End Function just add =ValidEmail(A1) in B1 say. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sb1920alk" wrote in message ... I have a large (~10,000) list of former clients in an excel file that was created when I pulled a report from my third party software. I've contracted with a newsletter company to help stay in touch with them. The newsletter company charges by entry so I need to delete any rows that aren't addresses. I've already found the marco that deletes duplicate entries, and I can easily sort to get rid of entries with no email address, now I need some way to delete entries that have something other than an email address in the "email address" column. The answer could be several things: a helper column that tests for wildcard@wildcard, or a macro that tests and deletes rows. I haven't been able to get anything to work yet. Any help is appreciated. Regards, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Need help finding non-duplicate email addresses... | Excel Discussion (Misc queries) | |||
Help needed...test score statistics | Excel Discussion (Misc queries) | |||
comparing test scores over a few years | Excel Discussion (Misc queries) | |||
copy/convert column email addresses Hyperlink "mailto:" excel97 | Excel Discussion (Misc queries) | |||
Copy Excel email addresses to GroupWise | Excel Discussion (Misc queries) |