Problem with new filter formula - error if cell has more than 250 characters

%3CLINGO-SUB%20id%3D%22lingo-sub-1606438%22%20slang%3D%22en-US%22%3EProblem%20with%20new%20filter%20formula%20-%20error%20if%20cell%20has%20more%20than%20250%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606438%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20filter%20formula%20to%20only%20show%20certain%20records%20on%20a%20summary%20page.%20However%2C%20if%20a%20cell%20has%20more%20than%20250%20characters%2C%20it%20returns%20an%20error%20in%20the%20spill%20field.%20Is%20there%20a%20way%20around%20this%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EJitixa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1606438%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606776%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20new%20filter%20formula%20-%20error%20if%20cell%20has%20more%20than%20250%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606776%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F768107%22%20target%3D%22_blank%22%3E%40Jitixa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20illustrate%20by%20the%20sample%20or%20be%20more%20specific%20in%20description%20of%20how%20do%20use%20these%20functions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20take%20a%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20442px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214189i4A433E6EAB60A4B6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20C2%3AC6%20we%20generate%20texts%20as%20%3DREPT(%22%3D%22%2CB2%3AB6)%20and%20filter%20this%20array%20below%20-%20it%20works%20with%20%26gt%3B256%20characters%20texts.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609604%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20new%20filter%20formula%20-%20error%20if%20cell%20has%20more%20than%20250%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609604%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%20for%20commenting%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20put%20a%20macro%20in%20place%20instead%20for%20now%2C%20but%20it%20would%20be%20good%20to%20know%20for%20future%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20Question%23%2C%20Question%20and%20Answer%20columns%20(simplified)%2C%20Yes%2FNo%20column%3C%2FP%3E%3CP%3EThe%20Answer%20column%20is%20where%20someone%20can%20type%20in%20text%2C%20but%20is%20quite%20often%20more%20than%20250%20characters%20in%20length.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20use%20the%20filter%20formula%20on%20Yes%20criteria%20from%20the%20Yes%2FNo%20column%2C%20the%20formula%20does%20not%20work%20if%20a%20cell%20in%20the%20Answer%20column%20has%20more%20than%20250%20characters.%20It%20brings%20up%20a%20Value%23%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20work%20around%20this%20character%20limitation%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610115%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20new%20filter%20formula%20-%20error%20if%20cell%20has%20more%20than%20250%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610115%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F768107%22%20target%3D%22_blank%22%3E%40Jitixa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20share%20us%20the%20sample%20with%20your%20error%20which%20will%20help%20us%20to%20guide%20you%20the%20correct%20solution%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20mor%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610748%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20new%20filter%20formula%20-%20error%20if%20cell%20has%20more%20than%20250%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610748%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20but%20just%20this%20evening%20I%20found%20that%20the%20filter%20function%20did%20not%20like%20being%20imbed%20into%20a%20if%20and%20iserror%20statement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20came%20on%20here%20to%20update%20and%20saw%20your%20reply.%20Many%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20know%20for%20next%20time%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi

 

I am using the filter formula to only show certain records on a summary page. However, if a cell has more than 250 characters, it returns an error in the spill field. Is there a way around this problem?

 

Thanks

Jitixa

4 Replies
Highlighted

@Jitixa 

Could you please illustrate by the sample or be more specific in description of how do use these functions.

 

Let take a sample

image.png

In C2:C6 we generate texts as =REPT("=",B2:B6) and filter this array below - it works with >256 characters texts.

Highlighted

@Sergei Baklan 

 

Thanks so much for commenting

 

I have put a macro in place instead for now, but it would be good to know for future use.

 

I have a table with Question#, Question and Answer columns (simplified), Yes/No column

The Answer column is where someone can type in text, but is quite often more than 250 characters in length.

 

When I use the filter formula on Yes criteria from the Yes/No column, the formula does not work if a cell in the Answer column has more than 250 characters. It brings up a Value# error.

 

I hope that makes sense.

 

Is there a work around this character limitation?

 

Highlighted

Hi @Jitixa 

 

Please share us the sample with your error which will help us to guide you the correct solution 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it mor

Highlighted

@Faraz Shaikh 

 

Thanks but just this evening I found that the filter function did not like being imbed into a if and iserror statement.

 

I just came on here to update and saw your reply. Many thanks

 

I'll know for next time