Filter Formula Adjustment required pls

[Arts]

Hi all

I have the below formula which works fine with just one minor adjustment required, is it possible to not display the 0 values under R&D results?

=FILTER(C4:D22,ISNUMBER(SEARCH(E4,C4:C22))+ISNUMBER(SEARCH(E4,D4:D22)),"no")

Current	new
Project 1	R&D 1		Project 1	R&D 1
Project 2	R&D 2		Project 2	R&D 2
Project 3	R&D 3		Project 3	R&D 3
Project 4	R&D 4		Project 4	R&D 4
Project 5	R&D 5		Project 5	R&D 5
Project 6	R&D 6		Project 6	R&D 6
Project 7	R&D 7		Project 7	R&D 7
Project 8	R&D 8		Project 8	R&D 8
Project 9	R&D 9		Project 9	R&D 9
Project 10			Project 10	0​
Project 11			Project 11	0​
Project 12			Project 12	0​
Project 13			Project 13	0​
Project 14			Project 14	0​
Project 15			Project 15	0​
Project 16			Project 16	0​
Project 17			Project 17	0​
Project 18			Project 18	0​
Project 19			Project 19	0​

 

[Fluff]

How about

=FILTER(C4:D22&"",ISNUMBER(SEARCH(E4,C4:C22))+ISNUMBER(SEARCH(E4,D4:D22)),"no")

 

[Arts]

How about

=FILTER(C4:D22&"",ISNUMBER(SEARCH(E4,C4:C22))+ISNUMBER(SEARCH(E4,D4:D22)),"no")

That is exactly what I wanted! Thank you !!

I was trying all sorts to wrap the Filter in a Filter (my attempt below) and not include blanks (as was taught online) by including <>"".

=FILTER(FILTER(C4:D22,ISNUMBER(SEARCH(E4,C4:C22))+ISNUMBER(SEARCH(E4,D4:D22)),"no"),FILTER(C4:D22,ISNUMBER(SEARCH(E4,C4:C22))+ISNUMBER(SEARCH(E4,D4:D22))<>""))

Why would that work? You are asking to filter that range and blanks but why would that then drop the 0's (the blanks) would really appreciate if you could explain.

 

[Fluff]

The &"" adds a nullstring to the values, which prevents Xl from displaying empty cells as 0

 

[Arts]

Am I taking the Filter function too literally here? (The Filter function mirrors the Filter selection when in a spreadsheet is how I am seeing it)

When you use the Filter function in excel you naturally select the values you want.

This isn't the same as selecting blanks in a Filter within a spredsheet then? This would then include the blanks if this was the case..

 

[Fluff]

You can filter out blank cells, but that would mean Project 10 onwards would not appear in the list.

 

[Arts]

You mean R&D9 onwards would not appear as they are blank (they are two separate columns) I don't think I'm explaining how I'm seeing it.

What you have done is add "" to the filter criteria. Adding a blank in a filter would select blanks or is that you have added a blank so it now picks up the text and the blank. Where there is no text in the blank therefore this is now showing just values with the blank after it.

I'm travelling home from work now and my phone battery is dying incase I don't respond or like comment pls don't think I am ignoring.

 

[Fluff]

I have not added a "" to the criteria, I added it to the cells being filtered. Which is a totally different thing.

 

[Arts]

So is there a space after each value now or am I still completely missing it?

I'll log onto work machine tomorrow and check this.

Thoroughly appreciate the out of hours responses Fluff, thank you.

 

[Fluff]

No there is no space, it's a nullstring (zero length string) that turns everything to text & thereby prevents an empty cell from showing as 0