Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Not in KC anymore
    Thanked 0 Times in 0 Posts

    Autofilter Limit? (XP)

    I'm working on a very large spreadsheet that has some cells containing up to 10,000 characters.

    I'm trying to use Autofilter to filter and display the lines that only contain certain words.

    I know the words are contained in the cells, but Autofilter is showing no results.

    Is there a limit to the number of characters? If so, is there another way of filtering that will display results based on word(s) contained in the cells?

    I've tried the <Find> tool, but all it does is locate...I still have to manually count.

    Thanks in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Autofilter Limit? (XP)

    It seems to me (at least in XL97) that it only looks within the first 256 chars, and if not found there it gives up and decides it is not there...

    You will also see that items longer than 256 chars are not in the autofilter list...


    A workaround would be to use a new column with FIND to locate the desired words in the column and then filter on this column for TRUE.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Vancouver, Br. Columbia, Canada
    Thanked 1 Time in 1 Post

    Re: Autofilter Limit? (XP)

    If you insert a "dummy" column you can use the Find function to return the location of the wanted text string in each cell. If the search string is not found, it will return an error ("#VALUE!" in the cell). You could sort or filter on the returned values - if you filter for a value > 0, the error condition will fail (actually, it will fail no matter what numeric value you search / sort on.

    Alternatively, you could use a formula like =(find("Search",<cell ref>)>0) where '<cell ref>' is the cell that you are testing in each row - this will return "TRUE" for instances where the search string was found, and the "#VALUE!" error otherwise. Again, you can search or sort on those values. I tested this with cells containing ~15,000 characters, and it seemed to work.

    Initially it seemed that filtering for cells containing the text I wanted was working as well, but as the number of cells with long text entries increased, I also ran into the same sorts of problems as you describe, so it seems like some sort of (memory ?) limit within Excel.

Posting Permissions

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