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

Copper 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

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

@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