Solved: Excel 2000 filter query

[johnni]

I have a long list of items some of which have rows ending with a *
I wanted to use auto-filter to isolate these particular rows but when I use Custom in auto-filter and choose 'contains' and in the right hand box insert a * then OK, all the rows show up, presumably because * means 'represents any series of characters'.
Is there a way round this?

johnni

 

[slurpee55]

Not knowing what else is in the cells, this may not work perfectly but should help.
In the first choice, select "ends with" and enter this: ?*?
In the second choice, select "contains" and enter this: a
However, if your data has "123a45*", that will also get selected, so...
Assuming your data is in column A, if you enter in a new column =RIGHT(A1,2), then copy and Paste Special, Values the information in that column and apply the same special filter listed above, you will only get the info you want.

 

[johnni]

I should say that where I have used an asterix it is always at the end of a line of text and preceded by a space; just in case this makes a difference.
I found it hard to understand why I need to enter 'a' next to the 'contains' box, and the =RIGHT(A1,A2) was even more baffling.

I did try to use Ctrl-H to replace all the asterisks with a different symbol but even here the search refused to 'find' any asterisks. I suppose I could do it all by hand.

Any other suggestions would be welcome.

johnni

 

[slurpee55]

If every item ends in " *", use a variation of the formula that I posted above, making it:
=RIGHT(A1,3) (assuming your data is in A). Drag it down next to all the data.
Then you will get a column of data that will look like
e *
a *
5 *
f *
a *
s *
2 *
etc. Then just put a filter on that new column and select
a *

ACK!!, ignore this - read the next post.

 

[slurpee55]

I have a long list of items some of which have rows ending with a *
I wanted to use auto-filter to isolate these particular rows but when I use Custom in auto-filter and choose 'contains' and in the right hand box insert a * then OK, all the rows show up, presumably because * means 'represents any series of characters'.
Is there a way round this?

johnni

Oh, just got to re-reading your post and your latest. I misread it thinking you wanted "rows ending with a *" - that is why I said to use an "a" in the special filter.
If you just want any item that ends with " *", use the =RIGHT(A1,2) and you will get a list of items (whatever the data may be) such as
12
er
*
44
fq
32
mn
*
*
90
etc.
Then just do a regular filter on this column and it will list the possible choices. Choose
*

 

[johnni]

Thanks for your last post; I now understand the logic and I inserted the extra column and entered =RIGHT(D1,1) (my data was originally in Column C but because of the extra column C became D instead. Is that right?

Anyway, it appeared to be right and the new column C now contains the last character of all the rows in D including the occasional asterisk. Good!

But when I tried to do a normal filter on C using 'equals' and * I still got ALL the content and NOT just the *. I assume this is because, as I mentioned earlier, * means 'represents any series of characters' and therefore I got the lot.

It does seem there is no answer to this other than manually changing the asterisk to something else.

johnni

 

[slurpee55]

No, I had managed to get it before, but can't now...hmm, do this instead.
Where you had the one formula in column C, enter instead:
=IF(RIGHT(D2,2)=" *",1,"")
This will give you a column with "1" rather than "*" - may not be quite all that you want. You could change the one (1) in the formula to anything you wanted - it only shows when the logical test is true.
Maybe:
=IF(RIGHT(D2,2)=" *","Johnni!!!!!!","") ?

 

[johnni]

YES - that works!! After I had used the filter using 1, I copied the filtered list to a new worksheet and deleted the extra column. Thank you very much for your efforts.

johnni

 

[slurpee55]

Glad to help - sorry for the confusion, too.

 

[johnni]

slurpee55, I have just found an excellent new way to filter my original list containing asterisks at the end of some of the rows. Over the weekend I borrowed an excellent, and very large book named 'Using Excel Version 5, Special edition' by Ron Person, published by QUE in 1993. It stated that I can use either the Filter command or Search and Replace with an asterisk by inserting a tilde ~ immediately before the asterisk; "This tilde tells Excel to treat the asterisk as normal text, not a wild card". And it works also in my Excel 2000 and probably later versions!!
But I am still grateful for your efforts on my behalf.

johnni

 

[slurpee55]

learn something every day!

 

[Zack Barresse]

The tilde is a special character just as the asterisk is. Use it to preceed the single character placeholder (?) as well as the any character placeholder (*). It works in all versions of Excel, btw.