Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Filtering - Limitations of the "Contains" option
I have a column with many values in it separated by a Pipe "|". In some
records the text string "QREG" is present. Yet when I use the auto - custom fileter with the contains options selected nad enter the text QREG, the filter does not work correctly and these records get filtered out instead of remaining. Is there limitations on the number of characters Excel can apply the contains options to? Can you tell me any limitations excel may have using the custom filter contains option? Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Filtering - Limitations of the "Contains" option
I cannot replicate your problem on my Excel 2003.
Pipe de-limited text containing QREG is properly filtered using "contains" I don't believe there are limitations but how many characters would be in a cell with "many values" Gord Dibben MS Excel MVP On Sun, 22 Jun 2008 08:06:00 -0700, Timothyg787 wrote: I have a column with many values in it separated by a Pipe "|". In some records the text string "QREG" is present. Yet when I use the auto - custom fileter with the contains options selected nad enter the text QREG, the filter does not work correctly and these records get filtered out instead of remaining. Is there limitations on the number of characters Excel can apply the contains options to? Can you tell me any limitations excel may have using the custom filter contains option? Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Filtering - Limitations of the "Contains" option
Hi Gord,
Thanks for the reply. What I have found is that if I move the text I am filtering for closer to the beginning of the text in the cell the filter than works. So, is it possible that the contains filter only works up until say the first 255 characters. Thanks, "Gord Dibben" wrote: I cannot replicate your problem on my Excel 2003. Pipe de-limited text containing QREG is properly filtered using "contains" I don't believe there are limitations but how many characters would be in a cell with "many values" Gord Dibben MS Excel MVP On Sun, 22 Jun 2008 08:06:00 -0700, Timothyg787 wrote: I have a column with many values in it separated by a Pipe "|". In some records the text string "QREG" is present. Yet when I use the auto - custom fileter with the contains options selected nad enter the text QREG, the filter does not work correctly and these records get filtered out instead of remaining. Is there limitations on the number of characters Excel can apply the contains options to? Can you tell me any limitations excel may have using the custom filter contains option? Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Filtering - Limitations of the "Contains" option
Hi again Gord,
I am using Excel 2000. I have tested further and find that if the character(s) I am filtering for is within the first 255 characters then the filter works. As soon as the character(s) being filtered for are beyond the 255 range the filter no longer works correctly. I used the LEN function to validate. I may have more than a 1,000 characters in any given cell so as a tool the filtering will not be useful if this limitation can not be altered. Is there any way to increase this limitation? Thanks, "Gord Dibben" wrote: I cannot replicate your problem on my Excel 2003. Pipe de-limited text containing QREG is properly filtered using "contains" I don't believe there are limitations but how many characters would be in a cell with "many values" Gord Dibben MS Excel MVP On Sun, 22 Jun 2008 08:06:00 -0700, Timothyg787 wrote: I have a column with many values in it separated by a Pipe "|". In some records the text string "QREG" is present. Yet when I use the auto - custom fileter with the contains options selected nad enter the text QREG, the filter does not work correctly and these records get filtered out instead of remaining. Is there limitations on the number of characters Excel can apply the contains options to? Can you tell me any limitations excel may have using the custom filter contains option? Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Filtering - Limitations of the "Contains" option
You can't change the behavior, but you can use a different technique:
http://contextures.com/xlautofilter02.html#String (from Debra Dalgleish's site) Timothyg787 wrote: Hi again Gord, I am using Excel 2000. I have tested further and find that if the character(s) I am filtering for is within the first 255 characters then the filter works. As soon as the character(s) being filtered for are beyond the 255 range the filter no longer works correctly. I used the LEN function to validate. I may have more than a 1,000 characters in any given cell so as a tool the filtering will not be useful if this limitation can not be altered. Is there any way to increase this limitation? Thanks, "Gord Dibben" wrote: I cannot replicate your problem on my Excel 2003. Pipe de-limited text containing QREG is properly filtered using "contains" I don't believe there are limitations but how many characters would be in a cell with "many values" Gord Dibben MS Excel MVP On Sun, 22 Jun 2008 08:06:00 -0700, Timothyg787 wrote: I have a column with many values in it separated by a Pipe "|". In some records the text string "QREG" is present. Yet when I use the auto - custom fileter with the contains options selected nad enter the text QREG, the filter does not work correctly and these records get filtered out instead of remaining. Is there limitations on the number of characters Excel can apply the contains options to? Can you tell me any limitations excel may have using the custom filter contains option? Thank you. -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Filtering - Limitations of the "Contains" option
Thanks for the assist Dave.
I had forgotten about that 255 char limitation. I think I now remember pointing someone to Debra's site some time ago for the same issue. As in.........before breakfast today<g Gord On Sun, 22 Jun 2008 13:10:51 -0500, Dave Peterson wrote: You can't change the behavior, but you can use a different technique: http://contextures.com/xlautofilter02.html#String (from Debra Dalgleish's site) Timothyg787 wrote: Hi again Gord, I am using Excel 2000. I have tested further and find that if the character(s) I am filtering for is within the first 255 characters then the filter works. As soon as the character(s) being filtered for are beyond the 255 range the filter no longer works correctly. I used the LEN function to validate. I may have more than a 1,000 characters in any given cell so as a tool the filtering will not be useful if this limitation can not be altered. Is there any way to increase this limitation? Thanks, "Gord Dibben" wrote: I cannot replicate your problem on my Excel 2003. Pipe de-limited text containing QREG is properly filtered using "contains" I don't believe there are limitations but how many characters would be in a cell with "many values" Gord Dibben MS Excel MVP On Sun, 22 Jun 2008 08:06:00 -0700, Timothyg787 wrote: I have a column with many values in it separated by a Pipe "|". In some records the text string "QREG" is present. Yet when I use the auto - custom fileter with the contains options selected nad enter the text QREG, the filter does not work correctly and these records get filtered out instead of remaining. Is there limitations on the number of characters Excel can apply the contains options to? Can you tell me any limitations excel may have using the custom filter contains option? Thank you. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Filtering - Limitations of the "Contains" option
If you hadn't replied, the OP wouldn't have clarified the question.
So count it as a team solution, er, team work-around! Gord Dibben wrote: Thanks for the assist Dave. I had forgotten about that 255 char limitation. I think I now remember pointing someone to Debra's site some time ago for the same issue. As in.........before breakfast today<g Gord On Sun, 22 Jun 2008 13:10:51 -0500, Dave Peterson wrote: You can't change the behavior, but you can use a different technique: http://contextures.com/xlautofilter02.html#String (from Debra Dalgleish's site) Timothyg787 wrote: Hi again Gord, I am using Excel 2000. I have tested further and find that if the character(s) I am filtering for is within the first 255 characters then the filter works. As soon as the character(s) being filtered for are beyond the 255 range the filter no longer works correctly. I used the LEN function to validate. I may have more than a 1,000 characters in any given cell so as a tool the filtering will not be useful if this limitation can not be altered. Is there any way to increase this limitation? Thanks, "Gord Dibben" wrote: I cannot replicate your problem on my Excel 2003. Pipe de-limited text containing QREG is properly filtered using "contains" I don't believe there are limitations but how many characters would be in a cell with "many values" Gord Dibben MS Excel MVP On Sun, 22 Jun 2008 08:06:00 -0700, Timothyg787 wrote: I have a column with many values in it separated by a Pipe "|". In some records the text string "QREG" is present. Yet when I use the auto - custom fileter with the contains options selected nad enter the text QREG, the filter does not work correctly and these records get filtered out instead of remaining. Is there limitations on the number of characters Excel can apply the contains options to? Can you tell me any limitations excel may have using the custom filter contains option? Thank you. -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Filtering - Limitations of the "Contains" option
Hi folks,
Works the charm, thank you the assistance. Sincerely, Tim "Dave Peterson" wrote: If you hadn't replied, the OP wouldn't have clarified the question. So count it as a team solution, er, team work-around! Gord Dibben wrote: Thanks for the assist Dave. I had forgotten about that 255 char limitation. I think I now remember pointing someone to Debra's site some time ago for the same issue. As in.........before breakfast today<g Gord On Sun, 22 Jun 2008 13:10:51 -0500, Dave Peterson wrote: You can't change the behavior, but you can use a different technique: http://contextures.com/xlautofilter02.html#String (from Debra Dalgleish's site) Timothyg787 wrote: Hi again Gord, I am using Excel 2000. I have tested further and find that if the character(s) I am filtering for is within the first 255 characters then the filter works. As soon as the character(s) being filtered for are beyond the 255 range the filter no longer works correctly. I used the LEN function to validate. I may have more than a 1,000 characters in any given cell so as a tool the filtering will not be useful if this limitation can not be altered. Is there any way to increase this limitation? Thanks, "Gord Dibben" wrote: I cannot replicate your problem on my Excel 2003. Pipe de-limited text containing QREG is properly filtered using "contains" I don't believe there are limitations but how many characters would be in a cell with "many values" Gord Dibben MS Excel MVP On Sun, 22 Jun 2008 08:06:00 -0700, Timothyg787 wrote: I have a column with many values in it separated by a Pipe "|". In some records the text string "QREG" is present. Yet when I use the auto - custom fileter with the contains options selected nad enter the text QREG, the filter does not work correctly and these records get filtered out instead of remaining. Is there limitations on the number of characters Excel can apply the contains options to? Can you tell me any limitations excel may have using the custom filter contains option? Thank you. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is the "zoom" option grayed out in "print preview?" | Excel Discussion (Misc queries) | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
pictures to work with "data" "sort" option | Excel Discussion (Misc queries) | |||
Disabling "SAVE AS" option under "File" | Excel Discussion (Misc queries) | |||
How do I get rid of "Top 10" and "Custom" choices Autofilter? | Excel Worksheet Functions |