1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2000 filter query

Discussion in 'Business Applications' started by johnni, Sep 29, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. johnni

    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
     
  2. slurpee55

    slurpee55

    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.
     
  3. johnni

    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
     
  4. slurpee55

    slurpee55

    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. :)
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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
    *
     
  6. johnni

    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
     
  7. slurpee55

    slurpee55

    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!!!!!!","") ? :)
     
  8. 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
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Glad to help - sorry for the confusion, too. :)
     
  10. 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
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    learn something every day! :)
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. :)
     
  13. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/754421

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice