When FILTER is used in an IF statement only 255 characters can be returned else #Value! error

[Paul_Mountford]

I'm having an issue trying to return the results of a filter without "0"s, because the value 0 actually means something in the data I am filtering.

To get around this I tried the following

=IF(ISBLANK(FILTER(DataSheet!$A$2:$V$5000,DataSheet1!$A$2:$A$5000=ThisSheet!$C$3)),"",FILTER(DataSheet!$A$2:$V$5000,DataSheet!$A$2:$A$5000=ThisSheet!$C$3))

But for any of the cells that have over 255 characters in DataSheet the above returns #Value! error

Any help for resolving the error or for displaying Filter results with 0 the character being kept but a null cell being represented by null or an empty string ""

[#2]

@Paul_Mountford 

No one formula in Excel could return blank value, thus ISBALNK(FILTER()) doesn't work, it's always FALSE. I guess the question is if C3 is blank. You may try

FILTER(DataSheet!$A$2:$V$5000,
  (DataSheet!$A$2:$A$5000=ThisSheet!$C$3) *
  (DataSheet!$A$2:$A$5000 <> "")
)

to exclude blanks from filtering. However, not sure which logic you'd kike to apply.

[#3]

Thanks but the code you've presented just returns the whole table.

It's a bit of a hack but you can execute the formula FILTER(DataSheet!... , DataSheet! = C3) and after the values are on the spreadsheet you can put ISBLANK() around the formula which will then display the true and false values, which is how you can get my original code to either display the text or the "", but in cells with over 255 characters it returns #Value!

[#4 · Accepted solution]

@Paul_Mountford 

I see, that's interesting. I guess that's the limitation of any if() function - IF(), COUNTIF(), etc. They don't work with constants more than 255 characters, and in your case before array converted to range this limitation works.

Variant, if you work with texts, could be

=LET(str,FILTER(DataSheet!$A$2:$V$5000,DataSheet!$A$2:$A$5000=ThisSheet!$C$3), LEFT(str,1500) )

It there are numbers within the range they will be returned as texts.

[#5]

@Sergei Baklan 

so far i know (shouldn't mean much :) in all Formulas we can insert 1024 charakters.

Did I get something wrong? (which is the case now and then :)

Excel specifications and limits

Thx

[#6]

@NikolinoDE 

Not exactly, we spear about variables, not about texts in grid. You may try

=IF(0, "a", "bbbbbbbbbb")

As soon as you repeat "b" in third parameter more than 255 times, you have an error

Practically is the same with COUNTIF() with long condition text, the only you have an error without explanations about contrition text length.

Same will be if simply ="bbbbb" if in above "b" is repeated more than 255 times. However,

=IF(0,A1,B1)
if in B1 is a long text, or
=IF(0, "a", REPT("b", 700) )

work.

Same with array formulas, before you convert array elements into grid range limit on variables will be applied.

[#7]

Thanks, I learned something new :)

[#8]

@NikolinoDE 

Me too, I didn't think about this before

[#9]

Thanks, appreciate the explanation as well as the workaround.

[#10]

@Paul_Mountford , you are welcome and thank you for interesting case