Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Suspicious Filter (Excel XP)

    Hi,
    Can anyone tell me why the filter in the attached file is including Sonia's name when I filter using *son? The auto filter is working accurately! I cannot find any reasons why the advanced filter is not pulling the correct data?

    Thanx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Suspicious Filter (Excel XP)

    According to the online help
    <hr>All items that begin with that text are filtered. For example, if you type the text Dav as a criterion, Microsoft Excel finds "Davolio," "David," and "Davis".<hr>
    In other words, Excel implicitly adds a * after the criteria.

    So you are effectively filtering on *son*, i.e. any value that contains son. If you want to filter on values containing son but not beginning with it, use ?*son.

    As far as I know, Advanced Filter doesn't provide a way to filter values ending on a specified string.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Suspicious Filter (Excel XP)

    <hr>As far as I know, Advanced Filter doesn't provide a way to filter values ending on a specified string<hr>

    You can do it with a formula. If your names are in column A (for example) you can use as the criterion:

    =RIGHT(A2,3)="son"

    [Note: this will require the criterion "header row" to be blank]

    Steve

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suspicious Filter (Excel XP)

    Thanks for clearing this up Hans. I find this VERY strange. Firstly that I have never come across it before, and second, that excel cannot filter for this criteria, unless modified the way you mensioned!

    Thanx for the solution though! I'll remember this as its come as a BIG surprise!

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Maybe Pieterse can give us some inside info!!
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suspicious Filter (Excel XP)

    Thanx Steve. You are also correct. I very seldom use formulas as criteria, but it is a very powerful alternative.
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Suspicious Filter (Excel XP)

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •