Forum Discussion

TheOldPuterMan's avatar
TheOldPuterMan
Brass Contributor
May 10, 2020

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...
Formulas and Functions
Sylvie_in_France's avatar
Sylvie_in_France
Brass Contributor

TheOldPuterMan 

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.

 

RC2pc's avatar
RC2pc
Copper Contributor
May 28, 2021

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. 

Sylvie_in_France 

 

EDIT: Now I am doing =IF(ISBLANK(FILTER(blahblahblah)),"",FILTER(blahblahblah))

  • welshsteve's avatar
    welshsteve
    Copper Contributor
    Jun 08, 2021

    RC2pc  the ISBlank worked for me thanks

     

    =IF(ISBLANK(FILTER(A2:M2911,B2:B2911=S4)),"",FILTER(A2:M2911,B2:B2911=S4))

     

    new formula

    • kandgfoto's avatar
      kandgfoto
      Copper Contributor
      Jan 05, 2022
      Works a charm
  • Patrick Matthews's avatar
    Patrick Matthews
    Copper Contributor
    Jul 25, 2022

    RC2pc 

     

    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))

Resources