How can I return blank cells as it is instead of printing as Zeros while using filter function

Question

["referring to the data shown in the image while using the filter function and getting the return values, How can I return blank cells as it is instead of printing as Zeros while using filter function"]

=FILTER(A2:C13,A2:A13=E1," ")

referring to the data shown in the image while using the filter function and getting the return values, How can I return blank cells as it is instead of printing as Zeros while using filter function

If no zeros are expected, you could use `=SUBSTITUTE(FILTER(A2:C13,A2:A13=E1," "),0,"")`. — VBasic2008, May 04 '23 at 00:37
@VBasic2008 Sir, i am sure using `SUBSTITUTE()` will replace the `0` in `10` and hence return as `1`. So it wont be right to use. — Mayukh Bhattacharya, May 04 '23 at 00:42

Mayukh Bhattacharya · Answer 1 · 2023-05-04T00:57:22.693

2

You could try:

• Formula used in cell E2

=LET(x,IF(A2:C13="","",A2:C13),FILTER(x,TAKE(x,,1)=E1))

Another possible way could be adding &"" in front of your array argument. Note that it will convert your numeric values to text so you can use a VALUE() function or multiple by 1 to convert it back to numeric when required for further calculation.

• Formula used in cell E8

=FILTER(A2:C13&"",A2:A13=E1," ")

Or, you can use as proposed by VBasic2008 Sir

• Formula used in cell E14

=FILTER(IF(A2:C13="","",A2:C13),A2:A13=E1," ")

edited May 04 '23 at 00:57

answered May 04 '23 at 00:35

Mayukh Bhattacharya

12,541
5
21
32

2

Your 2nd solution is converting numbers to strings. Maybe add `=FILTER(IF(A2:C13="","",A2:C13),A2:A13=E1," ")`. – VBasic2008 May 04 '23 at 00:53
1

Yeah I have mentioned that. – Mayukh Bhattacharya May 04 '23 at 00:54
1

Sorry, didn't read the explanation. I guess it's a bit too late. – VBasic2008 May 04 '23 at 00:54
1

Sir sorry I was updating the post with your alternative, also you dont have to be sorry about it. Thanks for sharing your thoughts =) – Mayukh Bhattacharya May 04 '23 at 00:57
Thanks for all the inputs, it's been a great help! – user21810668 May 11 '23 at 02:33
@user21810668 please mark as correct by clicking the check mark by the answer. – Mayukh Bhattacharya May 11 '23 at 09:54

David Leal · Answer 2 · 2023-05-04T03:16:35.483

There are multiple ways of solving it. I would say changing the Excel configuration would be the first option to consider to solve this issue since it doesn't require modifying the original formula: File -> Options -> Advanced and unmark the following highlighted option: Show a zero in cells that have zero value:

It applies only to the selected worksheet. Here is the output:

Not having this option available, for example when using Excel Web, then you need to use a formula. I prefer not to change the data type of the output and to do the transformation in the output rather than in the input, because the output is a subset of the input, so it would be more efficient to do any transformation in a reduced subset. For example:

=LET(f, FILTER(A2:C4, A2:A4=E1), IF(f="","",f))

or

=LET(f, FILTER(A2:C4, A2:A4=E1), IF(LEN(f),f,""))

Here is the output (having the referred Excel option marked):

By the way, this question should be marked as duplicated due to the following question: Excel FILTER() returning 0 for blank cells, but I like more the answers provided here than in the referred link and also the OP of this question was more precise in its formulation than in the referred question.

score 0 · Answer 3 · edited Aug 29 '23 at 05:13

0

I solved it by using the IF function. =IF(FILTER(Array, Array Conditions)=0, "",FILTER(Array, Array Conditions))

edited Aug 29 '23 at 05:13

starball

20,030
7
43
238

answered Aug 29 '23 at 04:28

Tino

1
1

How can I return blank cells as it is instead of printing as Zeros while using filter function

3 Answers3

Linked