Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Sort Invalid Email Addresses?
My customer base of 5,000+ records (columns a-l) has some invalid email
addresses in column g. The "@" is missing. I would like to have those particular email addresses sorted at the beginning of the list so that I can go through them and see if they can be corrected or deleted. Which formula would work best for this project? Thanks for your help. Best regards, victor |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Sort Invalid Email Addresses?
try data filter
datafilterautofiltercustomdoesnotcontain @ -- Don Guillett SalesAid Software "la90292" wrote in message ... My customer base of 5,000+ records (columns a-l) has some invalid email addresses in column g. The "@" is missing. I would like to have those particular email addresses sorted at the beginning of the list so that I can go through them and see if they can be corrected or deleted. Which formula would work best for this project? Thanks for your help. Best regards, victor |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Sort Invalid Email Addresses?
Hi Don:
Thank's for the suggestion. That worked fine; however, my customer list has been previously sorted using an "advanced filter" for the 13 western states only. So, when I use your suggestion to identify the invalid email address it shows all the records in all the states. How would I override this to only do the customers in the 13 western states with invalid email addresses? Thanks again for your time and consideration. Best regards, victor "Don Guillett" wrote in message ... try data filter datafilterautofiltercustomdoesnotcontain @ -- Don Guillett SalesAid Software "la90292" wrote in message ... My customer base of 5,000+ records (columns a-l) has some invalid email addresses in column g. The "@" is missing. I would like to have those particular email addresses sorted at the beginning of the list so that I can go through them and see if they can be corrected or deleted. Which formula would work best for this project? Thanks for your help. Best regards, victor |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Sort Invalid Email Addresses?
Since one is an advanced filter and one an auto filter how can you apply
them at the same time. I can not do them separately without showing all the records again. Or, how would I use write the formula above the email column using the advanced filter to produce the same results as the auto filter. I tried writing "does not contain @" above the email colum, but that didn't work. Best regards, victor Don Guillett wrote in message ... Couldn't you apply both filters? -- Don Guillett SalesAid Software "SeaTiger" wrote in message . com... Hi Don: Thank's for the suggestion. That worked fine; however, my customer list has been previously sorted using an "advanced filter" for the 13 western states only. So, when I use your suggestion to identify the invalid address it shows all the records in all the states. How would I override this to only do the customers in the 13 western states with invalid addresses? Thanks again for your time and consideration. Best regards, victor "Don Guillett" wrote in message ... try data filter datafilterautofiltercustomdoesnotcontain @ -- Don Guillett SalesAid Software "la90292" wrote in message ... My customer base of 5,000+ records (columns a-l) has some invalid addresses in column g. The "@" is missing. I would like to have those particular email addresses sorted at the beginning of the list so that I can go through them and see if they can be corrected or deleted. Which formula would work best for this project? Thanks for your help. Best regards, victor |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Sort Invalid Email Addresses?
Another suggestion would be to use advanced filter and copy to another
location, that would give you an unfiltered list separated from the original list and you can apply autofilter on that list -- Regards, Peo Sjoblom Portland, Oregon "la90292" wrote in message . com... Since one is an advanced filter and one an auto filter how can you apply them at the same time. I can not do them separately without showing all the records again. Or, how would I use write the formula above the email column using the advanced filter to produce the same results as the auto filter. I tried writing "does not contain @" above the email colum, but that didn't work. Best regards, victor Don Guillett wrote in message ... Couldn't you apply both filters? -- Don Guillett SalesAid Software "SeaTiger" wrote in message . com... Hi Don: Thank's for the suggestion. That worked fine; however, my customer list has been previously sorted using an "advanced filter" for the 13 western states only. So, when I use your suggestion to identify the invalid address it shows all the records in all the states. How would I override this to only do the customers in the 13 western states with invalid addresses? Thanks again for your time and consideration. Best regards, victor "Don Guillett" wrote in message ... try data filter datafilterautofiltercustomdoesnotcontain @ -- Don Guillett SalesAid Software "la90292" wrote in message ... My customer base of 5,000+ records (columns a-l) has some invalid addresses in column g. The "@" is missing. I would like to have those particular email addresses sorted at the beginning of the list so that I can go through them and see if they can be corrected or deleted. Which formula would work best for this project? Thanks for your help. Best regards, victor |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Sort Invalid Email Addresses?
Hi Peo:
That's what I did, but when I attempted to do the auto filter the previous records come up again. What do I need to do to the new list so that doesn't happen. Thanks, victor "Peo Sjoblom" wrote in message ... Another suggestion would be to use advanced filter and copy to another location, that would give you an unfiltered list separated from the original list and you can apply autofilter on that list -- Regards, Peo Sjoblom Portland, Oregon "la90292" wrote in message . com... Since one is an advanced filter and one an auto filter how can you apply them at the same time. I can not do them separately without showing all the records again. Or, how would I use write the formula above the email column using the advanced filter to produce the same results as the auto filter. I tried writing "does not contain @" above the email colum, but that didn't work. Best regards, victor Don Guillett wrote in message ... Couldn't you apply both filters? -- Don Guillett SalesAid Software "SeaTiger" wrote in message . com... Hi Don: Thank's for the suggestion. That worked fine; however, my customer list has been previously sorted using an "advanced filter" for the 13 western states only. So, when I use your suggestion to identify the invalid address it shows all the records in all the states. How would I override this to only do the customers in the 13 western states with invalid addresses? Thanks again for your time and consideration. Best regards, victor "Don Guillett" wrote in message ... try data filter datafilterautofiltercustomdoesnotcontain @ -- Don Guillett SalesAid Software "la90292" wrote in message ... My customer base of 5,000+ records (columns a-l) has some invalid addresses in column g. The "@" is missing. I would like to have those particular email addresses sorted at the beginning of the list so that I can go through them and see if they can be corrected or deleted. Which formula would work best for this project? Thanks for your help. Best regards, victor |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Sort Invalid Email Addresses?
I would copy the advanced filtered list to another sheet, thus out of the
way form any filters on the same sheet If you start from the sheet where you want to copy the list, assume the list is in Sheet1 and you want to filtered list in Sheet2, click in A1 in Sheet2, do datafilteradvanced filter, click in the list range and select the table in Sheet1, put in the criteria, then copy to click in a cell in Sheet2, do then filter and it will be in Sheet2, now you will have a filtered list without any interference, then apply the autofilter and filter 'til the cows come home :) -- Regards, Peo Sjoblom Portland, Oregon "SeaTiger" wrote in message . .. Hi Peo: That's what I did, but when I attempted to do the auto filter the previous records come up again. What do I need to do to the new list so that doesn't happen. Thanks, victor "Peo Sjoblom" wrote in message ... Another suggestion would be to use advanced filter and copy to another location, that would give you an unfiltered list separated from the original list and you can apply autofilter on that list -- Regards, Peo Sjoblom Portland, Oregon "la90292" wrote in message . com... Since one is an advanced filter and one an auto filter how can you apply them at the same time. I can not do them separately without showing all the records again. Or, how would I use write the formula above the email column using the advanced filter to produce the same results as the auto filter. I tried writing "does not contain @" above the email colum, but that didn't work. Best regards, victor Don Guillett wrote in message ... Couldn't you apply both filters? -- Don Guillett SalesAid Software "SeaTiger" wrote in message . com... Hi Don: Thank's for the suggestion. That worked fine; however, my customer list has been previously sorted using an "advanced filter" for the 13 western states only. So, when I use your suggestion to identify the invalid address it shows all the records in all the states. How would I override this to only do the customers in the 13 western states with invalid addresses? Thanks again for your time and consideration. Best regards, victor "Don Guillett" wrote in message ... try data filter datafilterautofiltercustomdoesnotcontain @ -- Don Guillett SalesAid Software "la90292" wrote in message ... My customer base of 5,000+ records (columns a-l) has some invalid addresses in column g. The "@" is missing. I would like to have those particular email addresses sorted at the beginning of the list so that I can go through them and see if they can be corrected or deleted. Which formula would work best for this project? Thanks for your help. Best regards, victor |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Sort Invalid Email Addresses?
Okay, that worked. It took awhile because you have to make sure you have the
right sheets before each range and criteria so I kept getting error messages. Now I'm ready to bring the cows home and want to auto filter my email addresses to identify those cells that are not blank and do not have the @. I figured out the "does not contain" @, but what do I use for the identifying the records that are not blank?? Really, thanks alot for your help. Best regards, victor My customer list is file is in sheet 2. My list range is : $A$15:$l$5443. My criteria range is: $A$1:$l$14. Peo Sjoblom wrote in message ... I would copy the advanced filtered list to another sheet, thus out of the way form any filters on the same sheet If you start from the sheet where you want to copy the list, assume the list is in Sheet1 and you want to filtered list in Sheet2, click in A1 in Sheet2, do datafilteradvanced filter, click in the list range and select the table in Sheet1, put in the criteria, then copy to click in a cell in Sheet2, do then filter and it will be in Sheet2, now you will have a filtered list without any interference, then apply the autofilter and filter 'til the cows come home :) -- Regards, Peo Sjoblom Portland, Oregon "SeaTiger" wrote in message . .. Hi Peo: That's what I did, but when I attempted to do the auto filter the previous records come up again. What do I need to do to the new list so that doesn't happen. Thanks, victor "Peo Sjoblom" wrote in message ... Another suggestion would be to use advanced filter and copy to another location, that would give you an unfiltered list separated from the original list and you can apply autofilter on that list -- Regards, Peo Sjoblom Portland, Oregon "la90292" wrote in message . com... Since one is an advanced filter and one an auto filter how can you apply them at the same time. I can not do them separately without showing all the records again. Or, how would I use write the formula above the email column using the advanced filter to produce the same results as the auto filter. I tried writing "does not contain @" above the email colum, but that didn't work. Best regards, victor Don Guillett wrote in message ... Couldn't you apply both filters? -- Don Guillett SalesAid Software "SeaTiger" wrote in message . com... Hi Don: Thank's for the suggestion. That worked fine; however, my customer list has been previously sorted using an "advanced filter" for the 13 western states only. So, when I use your suggestion to identify the invalid address it shows all the records in all the states. How would I override this to only do the customers in the 13 western states with invalid addresses? Thanks again for your time and consideration. Best regards, victor "Don Guillett" wrote in message ... try data filter datafilterautofiltercustomdoesnotcontain @ -- Don Guillett SalesAid Software "la90292" wrote in message ... My customer base of 5,000+ records (columns a-l) has some invalid addresses in column g. The "@" is missing. I would like to have those particular email addresses sorted at the beginning of the list so that I can go through them and see if they can be corrected or deleted. Which formula would work best for this project? Thanks for your help. Best regards, victor |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Sort Invalid Email Addresses?
OK, from autofilter dropdown select custom, then does not contain @
AND does not equal and leave the box to the right empty -- Regards, Peo Sjoblom Portland, Oregon "la90292" wrote in message . com... Okay, that worked. It took awhile because you have to make sure you have the right sheets before each range and criteria so I kept getting error messages. Now I'm ready to bring the cows home and want to auto filter my email addresses to identify those cells that are not blank and do not have the @. I figured out the "does not contain" @, but what do I use for the identifying the records that are not blank?? Really, thanks alot for your help. Best regards, victor My customer list is file is in sheet 2. My list range is : $A$15:$l$5443. My criteria range is: $A$1:$l$14. Peo Sjoblom wrote in message ... I would copy the advanced filtered list to another sheet, thus out of the way form any filters on the same sheet If you start from the sheet where you want to copy the list, assume the list is in Sheet1 and you want to filtered list in Sheet2, click in A1 in Sheet2, do datafilteradvanced filter, click in the list range and select the table in Sheet1, put in the criteria, then copy to click in a cell in Sheet2, do then filter and it will be in Sheet2, now you will have a filtered list without any interference, then apply the autofilter and filter 'til the cows come home :) -- Regards, Peo Sjoblom Portland, Oregon "SeaTiger" wrote in message . .. Hi Peo: That's what I did, but when I attempted to do the auto filter the previous records come up again. What do I need to do to the new list so that doesn't happen. Thanks, victor "Peo Sjoblom" wrote in message ... Another suggestion would be to use advanced filter and copy to another location, that would give you an unfiltered list separated from the original list and you can apply autofilter on that list -- Regards, Peo Sjoblom Portland, Oregon "la90292" wrote in message . com... Since one is an advanced filter and one an auto filter how can you apply them at the same time. I can not do them separately without showing all the records again. Or, how would I use write the formula above the email column using the advanced filter to produce the same results as the auto filter. I tried writing "does not contain @" above the email colum, but that didn't work. Best regards, victor Don Guillett wrote in message ... Couldn't you apply both filters? -- Don Guillett SalesAid Software "SeaTiger" wrote in message . com... Hi Don: Thank's for the suggestion. That worked fine; however, my customer list has been previously sorted using an "advanced filter" for the 13 western states only. So, when I use your suggestion to identify the invalid address it shows all the records in all the states. How would I override this to only do the customers in the 13 western states with invalid addresses? Thanks again for your time and consideration. Best regards, victor "Don Guillett" wrote in message ... try data filter datafilterautofiltercustomdoesnotcontain @ -- Don Guillett SalesAid Software "la90292" wrote in message ... My customer base of 5,000+ records (columns a-l) has some invalid addresses in column g. The "@" is missing. I would like to have those particular email addresses sorted at the beginning of the list so that I can go through them and see if they can be corrected or deleted. Which formula would work best for this project? Thanks for your help. Best regards, victor |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Sort Invalid Email Addresses?
Peo. the cows are finally in the barn...thanks!!!
Peo Sjoblom wrote in message ... OK, from autofilter dropdown select custom, then does not contain @ AND does not equal and leave the box to the right empty -- Regards, Peo Sjoblom Portland, Oregon "la90292" wrote in message . com... Okay, that worked. It took awhile because you have to make sure you have the right sheets before each range and criteria so I kept getting error messages. Now I'm ready to bring the cows home and want to auto filter my email addresses to identify those cells that are not blank and do not have the @. I figured out the "does not contain" @, but what do I use for the identifying the records that are not blank?? Really, thanks alot for your help. Best regards, victor My customer list is file is in sheet 2. My list range is : $A$15:$l$5443. My criteria range is: $A$1:$l$14. Peo Sjoblom wrote in message ... I would copy the advanced filtered list to another sheet, thus out of the way form any filters on the same sheet If you start from the sheet where you want to copy the list, assume the list is in Sheet1 and you want to filtered list in Sheet2, click in A1 in Sheet2, do datafilteradvanced filter, click in the list range and select the table in Sheet1, put in the criteria, then copy to click in a cell in Sheet2, do then filter and it will be in Sheet2, now you will have a filtered list without any interference, then apply the autofilter and filter 'til the cows come home :) -- Regards, Peo Sjoblom Portland, Oregon "SeaTiger" wrote in message . .. Hi Peo: That's what I did, but when I attempted to do the auto filter the previous records come up again. What do I need to do to the new list so that doesn't happen. Thanks, victor "Peo Sjoblom" wrote in message ... Another suggestion would be to use advanced filter and copy to another location, that would give you an unfiltered list separated from the original list and you can apply autofilter on that list -- Regards, Peo Sjoblom Portland, Oregon "la90292" wrote in message . com... Since one is an advanced filter and one an auto filter how can you apply them at the same time. I can not do them separately without showing all the records again. Or, how would I use write the formula above the column using the advanced filter to produce the same results as the auto filter. I tried writing "does not contain @" above the email colum, but that didn't work. Best regards, victor Don Guillett wrote in message ... Couldn't you apply both filters? -- Don Guillett SalesAid Software "SeaTiger" wrote in message . com... Hi Don: Thank's for the suggestion. That worked fine; however, my customer list has been previously sorted using an "advanced filter" for the 13 western states only. So, when I use your suggestion to identify the invalid address it shows all the records in all the states. How would I override this to only do the customers in the 13 western states with invalid addresses? Thanks again for your time and consideration. Best regards, victor "Don Guillett" wrote in message ... try data filter datafilterautofiltercustomdoesnotcontain @ -- Don Guillett SalesAid Software "la90292" wrote in message ... My customer base of 5,000+ records (columns a-l) has some invalid addresses in column g. The "@" is missing. I would like to have those particular email addresses sorted at the beginning of the list so that I can go through them and see if they can be corrected or deleted. Which formula would work best for this project? Thanks for your help. Best regards, victor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort in excel using end of email address | Excel Worksheet Functions | |||
Change email addresses | Excel Discussion (Misc queries) | |||
email addresses in cells | Excel Discussion (Misc queries) | |||
How do I input email addresses in Excel without the hyperlink, | Excel Discussion (Misc queries) | |||
How do you sort a column of email addresses by domain name in Exc. | Excel Discussion (Misc queries) |