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

New Contributor



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?




4 Replies


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


Let take a sample


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

@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?


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

@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