Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter numbers as text (2002 SP3)

    Hi y'all,
    can anyone give me a clue as to how I go about filtering to see all rows that do not contain the string '0123 in the attached spreadsheet?

    TIA

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

    Re: Filter numbers as text (2002 SP3)

    None of the cells in the spreadsheet you attached contain the string '0123', so I don't know what you need.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter numbers as text (2002 SP3)

    Silly me I meant to write '0321

  4. #4
    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: Filter numbers as text (2002 SP3)

    Custom filter:
    "does not contain"
    0321

    Seems to work

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter numbers as text (2002 SP3)

    Ahh, I was trying "Does not equal", which doesn't seem to work although one would have thought it should.

    Now is it possible to perform this filter using Advanced Filter"?
    (The ultimate filter criteria will require the use of advanced filter)

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

    Re: Filter numbers as text (2002 SP3)

    Yes, use a criteria range that looks like this:

    <table border=1><td>Criteria</td><td>=ISERROR(FIND("0321",B2))</td></table>
    (the formula must refer to the first row of the data)

  7. #7
    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: Filter numbers as text (2002 SP3)

    It does not work since it converts the "text-number"(0123) to a number (123) in its routine.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter numbers as text (2002 SP3)

    So it seems that Excel triumphs again. Why can't it just leave one's data alone!

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

    Re: Filter numbers as text (2002 SP3)

    <P ID="edit" class=small>(Edited by Rudi on 10-May-05 11:08. Sorry, I misread your post...You want records that DO NOT contain 0321. In that case Hans's formula will work. But you must still not have a column heading for formula criteria!)</P>Unless I'm missing the boat here, I was able to filter '0321 in a list by using =NOT(ISERROR(FIND("0321",B2))) as the criteria for the advanced filter. Remember to specify a blank cell as the column heading for a formula acting as criteria!
    Regards,
    Rudi

  10. #10
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter numbers as text (2002 SP3)

    Thanks Rudi, maybe the blank colum header was the trick. It still shouldn't be this difficult!

    Any normal user would expect at anto filter of "does not equal" 0123 (which appears in the drop down list, to work, rather than having to use "does not contain", which of course will not do exactly the same thing.

    Thanks to all who helped reach this solution.

  11. #11
    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: Filter numbers as text (2002 SP3)

    The problem is he wanted to do it with a custom autofilter not advanced filter.

    The autofilter does not "work" the way most think it should in this regard...

    Steve

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

    Re: Filter numbers as text (2002 SP3)

    Not according to <post#=477839>post 477839</post#>

    Paul needed the adv. filter!

    Anyhow...the answer is available in both formats...to keep everyone happy! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rudi

  13. #13
    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: Filter numbers as text (2002 SP3)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>Sorry. You are correct

    I was only thinking about the original question and the original post...

    Steve

Posting Permissions

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