Removing 0s

[heroharold99]

Hi everyone, Excel newbie here. I'm putting together a searchable database for my work and have managed to very inelegantly create a very crude search function. The problem is that the cells without any entries are displayed as 0s. Would yall be able to advise on how I might remove those 0s? Many thanks in advance!! The forumla I built is:

=FILTER(Table1,ISNUMBER(SEARCH(G2,Table1[Name of Company/Organization]))+ISNUMBER(SEARCH(G2,Table1[Case Lead Type]))+ISNUMBER(SEARCH(G2,Table1[Origin]))+ISNUMBER(SEARCH(G2,Table1[Year Founded]))+ISNUMBER(SEARCH(G2,Table1[Industry (primary)]))+ISNUMBER(SEARCH(G2,Table1[Industry (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Industry (tertiary)]))+ISNUMBER(SEARCH(G2,Table1[Completed IPO?]))+ISNUMBER(SEARCH(G2,Table1[Latest Revenue (millions USD)]))+ISNUMBER(SEARCH(G2,Table1[Revenue Type]))+ISNUMBER(SEARCH(G2,Table1[Lastest Valuation (millions USD)]))+ISNUMBER(SEARCH(G2,Table1[Short Description of Main Product/Service]))+ISNUMBER(SEARCH(G2,Table1[Key Innovation]))+ISNUMBER(SEARCH(G2,Table1[Name and Position of Company Contact]))+ISNUMBER(SEARCH(G2,Table1[Dilemma (primary)]))+ISNUMBER(SEARCH(G2,Table1[Dilemma (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Discussion Topic (primary)]))+ISNUMBER(SEARCH(G2,Table1[Discussion Topic (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Academic Unit (primary)]))+ISNUMBER(SEARCH(G2,Table1[Academic Unit (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Academic Unit (tertiary)]))+ISNUMBER(SEARCH(G2,Table1[Status]))+ISNUMBER(SEARCH(G2,Table1[Remarks])),"No Records Found")

 

[Fluff]

Hi & welcome to MrExcel.
How about

=let(f,FILTER(Table1,ISNUMBER(SEARCH(G2,Table1[Name of Company/Organization]))+ISNUMBER(SEARCH(G2,Table1[Case Lead Type]))+ISNUMBER(SEARCH(G2,Table1[Origin]))+ISNUMBER(SEARCH(G2,Table1[Year Founded]))+ISNUMBER(SEARCH(G2,Table1[Industry (primary)]))+ISNUMBER(SEARCH(G2,Table1[Industry (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Industry (tertiary)]))+ISNUMBER(SEARCH(G2,Table1[Completed IPO?]))+ISNUMBER(SEARCH(G2,Table1[Latest Revenue (millions USD)]))+ISNUMBER(SEARCH(G2,Table1[Revenue Type]))+ISNUMBER(SEARCH(G2,Table1[Lastest Valuation (millions USD)]))+ISNUMBER(SEARCH(G2,Table1[Short Description of Main Product/Service]))+ISNUMBER(SEARCH(G2,Table1[Key Innovation]))+ISNUMBER(SEARCH(G2,Table1[Name and Position of Company Contact]))+ISNUMBER(SEARCH(G2,Table1[Dilemma (primary)]))+ISNUMBER(SEARCH(G2,Table1[Dilemma (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Discussion Topic (primary)]))+ISNUMBER(SEARCH(G2,Table1[Discussion Topic (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Academic Unit (primary)]))+ISNUMBER(SEARCH(G2,Table1[Academic Unit (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Academic Unit (tertiary)]))+ISNUMBER(SEARCH(G2,Table1[Status]))+ISNUMBER(SEARCH(G2,Table1[Remarks])),"No Records Found"),if(f="","",f))

 

[heroharold99]

Hi & welcome to MrExcel.
How about

=let(f,FILTER(Table1,ISNUMBER(SEARCH(G2,Table1[Name of Company/Organization]))+ISNUMBER(SEARCH(G2,Table1[Case Lead Type]))+ISNUMBER(SEARCH(G2,Table1[Origin]))+ISNUMBER(SEARCH(G2,Table1[Year Founded]))+ISNUMBER(SEARCH(G2,Table1[Industry (primary)]))+ISNUMBER(SEARCH(G2,Table1[Industry (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Industry (tertiary)]))+ISNUMBER(SEARCH(G2,Table1[Completed IPO?]))+ISNUMBER(SEARCH(G2,Table1[Latest Revenue (millions USD)]))+ISNUMBER(SEARCH(G2,Table1[Revenue Type]))+ISNUMBER(SEARCH(G2,Table1[Lastest Valuation (millions USD)]))+ISNUMBER(SEARCH(G2,Table1[Short Description of Main Product/Service]))+ISNUMBER(SEARCH(G2,Table1[Key Innovation]))+ISNUMBER(SEARCH(G2,Table1[Name and Position of Company Contact]))+ISNUMBER(SEARCH(G2,Table1[Dilemma (primary)]))+ISNUMBER(SEARCH(G2,Table1[Dilemma (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Discussion Topic (primary)]))+ISNUMBER(SEARCH(G2,Table1[Discussion Topic (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Academic Unit (primary)]))+ISNUMBER(SEARCH(G2,Table1[Academic Unit (secondary)]))+ISNUMBER(SEARCH(G2,Table1[Academic Unit (tertiary)]))+ISNUMBER(SEARCH(G2,Table1[Status]))+ISNUMBER(SEARCH(G2,Table1[Remarks])),"No Records Found"),if(f="","",f))

Amazing. that did it! Thank you kindly~~

 

[Fluff]

You're welcome & thanks for the feedback.

 

[heroharold99]

Hi Fluff, I'm hoping to seek your advice again - I've added another column (in red in the formula below) that contains links to other files as part of the searchable database. The idea is so that the user can click on the hyperlink and access the relevant files directly. But since this is an array, the hyperlinks are not clickable. Do you know of a way to get around that? Many thanks in time!!

=LET(f,FILTER(Table1,ISNUMBER(SEARCH(O2,Table1[Name of Company/Organization]))+ISNUMBER(SEARCH(O2,Table1[Case Lead Type]))+ISNUMBER(SEARCH(O2,Table1[Origin]))+ISNUMBER(SEARCH(O2,Table1[Year Founded]))+ISNUMBER(SEARCH(O2,Table1[Industry (primary)]))+ISNUMBER(SEARCH(O2,Table1[Industry (secondary)]))+ISNUMBER(SEARCH(O2,Table1[Industry (tertiary)]))+ISNUMBER(SEARCH(O2,Table1[Company Type]))+ISNUMBER(SEARCH(O2,Table1[Latest Revenue (millions USD)]))+ISNUMBER(SEARCH(O2,Table1[Revenue Type]))+ISNUMBER(SEARCH(O2,Table1[Lastest Valuation (millions USD)]))+ISNUMBER(SEARCH(O2,Table1[Profitable?]))+ISNUMBER(SEARCH(O2,Table1[Main Product/Service]))+ISNUMBER(SEARCH(O2,Table1[Key Innovation]))+ISNUMBER(SEARCH(O2,Table1[Company Contact]))+ISNUMBER(SEARCH(O2,Table1[Dilemma (primary)]))+ISNUMBER(SEARCH(O2,Table1[Dilemma (secondary)]))+ISNUMBER(SEARCH(O2,Table1[Topic (primary)]))+ISNUMBER(SEARCH(O2,Table1[Topic (secondary)]))+ISNUMBER(SEARCH(O2,Table1[HBS Unit (primary)]))+ISNUMBER(SEARCH(O2,Table1[HBS Unit (secondary)]))+ISNUMBER(SEARCH(O2,Table1[HBS Unit (tertiary)]))+ISNUMBER(SEARCH(O2,Table1[Status]))+ISNUMBER(SEARCH(O2,Table1[Link]))+ISNUMBER(SEARCH(O2,Table1[Remarks])),"No Records Found"),IF(f="","",f))

 

[Fluff]

Afraid not as I hardly ever use hyperlinks. I suggest that you start a new thread.