Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering specific numbers (XP)

    Within a list I have a column of code number 155524, 145513, 175514, etc. I want to filter out those values that contain the sequence 55 BUT to exclude the sequence 555. How could I set this up using an Advanced Filter? Andy.

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

    Re: Filtering specific numbers (XP)

    Is it always only the middle 2 5's that need to be extracted?
    Regards,
    Rudi

  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: Filtering specific numbers (XP)

    If your header row is in 1 so the first datum is in row 2 and in values are in col A (change as appropriate), you can use this criterion:

    =AND(ISNUMBER(FIND("55",A2)),NOT(ISNUMBER(FIND("55 5",A2))))

    Remember for a formula the criterion header is left 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: Filtering specific numbers (XP)

    Thanx Steve,
    Your reply has refreshed my memory and reminded me that a formula cannot have a Column Heading when acting as a Criteria Range! This is a valuable reminder!!!

    Andrew:
    Here is an attachment that shows Steve's reply in practice... Remember to include a blank cell as a column heading in the criteria range.
    Regards,
    Rudi

Posting Permissions

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