Find/Replace or Sort with Asterisk ( * ) as Character, not wildcard 2

[attrofy]

Is it possible to sort on an asterisk as a character, and not as a wildcard??? I saw some early threads about Char(42) and the tilde (~) being used, but that tries to find text strings.

The "*" only returns anything contained in quotes.

Just so you know, I have a database of about 19,000+ customers. Some of the customers have an asterisk after their last name (dead, bad account, slow pay, etc). I want to remove these from the list. Anyway to sort all of the asterisks together for review before deletion??

If any macro or VBA codes reccomended, go slow (and use small words). I am slowly begining to grasp the whole VBA concept, but I need specific directions.

Also, just to clarify, this is just a plane-jane customer database
Last Name
First Name
Address
State
Zip
Phone

So if there is a macro to delete the entry, I need to delete the entire row, not just the entry in that column.

Thanks in advance.
Russell

 

[onedtent]

use ~* to find

 

[attrofy]

Thanks for the help, I must have tried that in quotes or something the first time around.

Any way to sort by Asterisk??

 

[desireeg]

Russell
I would suggest that you insert an additional column and use the formula =RIGHT(A1,1) (with A1 being the cell address of the last name field). This formula returns the rightmost characters in a text string. In your case, it will return the Asterisk on the desired records. Then sort your database by that column.

Hope this helps!
Desiree

 

[DaleWatson123321]

Hi Russell,

In answer to your last (unanswered) question - about sorting by Asterisk... I've developed an example model for you that includes VBA code attached to buttons to perform the following. Note: The criteria is currently set to deal with those records containing an asterisk in the LastName field.

Please note that I've removed the space in the field name. This is REQUIRED in this particular case. As a good practice, one should NOT use spaces in field names.

1) Extract the data (to a separate sheet)

2) Filter-in-place

3) Delete Filtered Records

4) Show All Data

NOTE: If you've attempted to extract data to a SEPARATE sheet using Excel's menu, you would have discovered a BUG - that tells you it's NOT possible. Indeed, it's NOT via the menu because the ERROR message BUG stops you in your tracks. However this IS possible using VBA.

Another BUG can surface when you attempt to place the criteria on a SEPARATE sheet. However, this is not only possible, but it's "preferable". When placing criteria on the SAME sheet as your data, it's not only cumbersome, but Excel "gets confused" - and it can become a "nightmare".

This example file should help considerably in understanding Excel's "database functionality".

If you email me, I'll send you the file via return email.

I hope this helps. After reviewing the file, please advise as to how you make out with it.

Also, if anyone else would like the file, please feel free to ask. When emailing, please refer to: "Find/Replace or Sort with Asterisk".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[attrofy]

Thank you both for your help. I will try both suggestions. Desireeg, I can see how your approach could be helpful with other spreadsheets.

Russell

 

[attrofy]

Purple star for you Dale for going above and beyond. You seemed to have taken the concept from my e-mail and applied it to the spreadsheet in a matter of moments. It looks like it is going to work perfectly. I unfortunately won't be able to apply it in real life ofr some time yet, but the preliminary tests worked great....

Thanks again for all of your help.

Russell