Forum Discussion
Filter function returning a "0" (Zero) when the source cell is blank or null
Hello all, I'm hoping that this isn't too obvious but I've noticed this situation several times and have had to work around it. I have a situation where the output of a FILTER function is cr...
Hi
I've been faced with the same problem.
To solve it, I enclose the FILTER function in a TEXT function.
=TEXT( FILTER( .... ), "" )
This keeps all texts in their normal format, but converts any number in an empty string.
Of course, il your source data contains numbers and you want to keep them, this is probably not the right solution.
This was so close to solving my issue.
I have it pulling people and it was showing a 0 in the email address column if they didn't have an email address. Your TEXT() solution fixed that. Unfortunately I was also trying to pull in phone numbers.
EDIT: Now I am doing =IF(ISBLANK(FILTER(blahblahblah)),"",FILTER(blahblahblah))
I just used a similar approach, except that I also used LET to avoid having the repetition:
=LET(Grid,tblAttributes[#Data],Selected,tblAttributes[Selected],FILTER(IF(ISBLANK(Grid),"",Grid),Selected=TRUE))
RC2pc the ISBlank worked for me thanks
=IF(ISBLANK(FILTER(A2:M2911,B2:B2911=S4)),"",FILTER(A2:M2911,B2:B2911=S4))
new formula
- Works a charm
