Solved: Excel 2000 filter query

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

johnni

Thread Starter
Joined
Dec 16, 2003
Messages
786
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
 
Joined
Oct 20, 2004
Messages
7,837
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

Thread Starter
Joined
Dec 16, 2003
Messages
786
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
 
Joined
Oct 20, 2004
Messages
7,837
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. :)
 
Joined
Oct 20, 2004
Messages
7,837
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

Thread Starter
Joined
Dec 16, 2003
Messages
786
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
 
Joined
Oct 20, 2004
Messages
7,837
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

Thread Starter
Joined
Dec 16, 2003
Messages
786
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
 

johnni

Thread Starter
Joined
Dec 16, 2003
Messages
786
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
 
Joined
Jul 25, 2004
Messages
5,458
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. :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top