Results 1 to 2 of 2

Thread: Limitations of AutoFilter on Long Text Strings?

  1. #1

    Limitations of AutoFilter on Long Text Strings?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I haven't been able to find an answer to this yet. Hopefully someone can help. Here's the situation...

    I have a spreadsheet containing about 20 columns and about 200 rows (a list of sorts). One of the columns contains a text string. This text string can be anywhere from being blank, to having 4000 to 5000 characters. It basically contains a set of names of people who subscribed to the program identified on that record.

    I'm trying a simple autofilter where I look for a persons name in that text string and filter to all the rows containing his/her name. This works at times and at times doesn't. It basically depends upon how far down in the text string that persons name appears. If it is near the beginning, the filter works fine. But if it is out near the end the filter fails.

    It seems there is some sort of limitation on the size of a string that can be searched. If I simply to a FIND from the excel menu and search for that same name that failed in the filter, it also fails in the FIND.

    Yes, I'm absolutely certain that it is not a spelling issue. I've tried with and without wildcards and again the wildcards work fine if the name is early in the string but fail if the name is late in the string.

    Has anyone run up against this sort of thing, and/or does anyone have any suggestions for a workaround?


    Thanks in advance.

  2. #2

    Answered my own question

    Quote Originally Posted by ruhlanma View Post
    I haven't been able to find an answer to this yet. Hopefully someone can help. Here's the situation...

    I have a spreadsheet containing about 20 columns and about 200 rows (a list of sorts). One of the columns contains a text string. This text string can be anywhere from being blank, to having 4000 to 5000 characters. It basically contains a set of names of people who subscribed to the program identified on that record.

    I'm trying a simple autofilter where I look for a persons name in that text string and filter to all the rows containing his/her name. This works at times and at times doesn't. It basically depends upon how far down in the text string that persons name appears. If it is near the beginning, the filter works fine. But if it is out near the end the filter fails.

    It seems there is some sort of limitation on the size of a string that can be searched. If I simply to a FIND from the excel menu and search for that same name that failed in the filter, it also fails in the FIND.

    Yes, I'm absolutely certain that it is not a spelling issue. I've tried with and without wildcards and again the wildcards work fine if the name is early in the string but fail if the name is late in the string.

    Has anyone run up against this sort of thing, and/or does anyone have any suggestions for a workaround?


    Thanks in advance.
    I found a solution. Apparently there is a 255 character limit on excel searches. Any characters beyond 255 are not searchable. A workaround is to create a new column, use the search function and test for a positive result. Then filter for true on that column.

Posting Permissions

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