Forum Discussion

GustavJ's avatar
GustavJ
Copper Contributor
Mar 15, 2024

Search bar - if blank, show nothing

So i'm still pretty new to excel, trying to make a contact list and applying a search bar to it.

 

So I've gotten the searching part and and that done, its just that if the search bar is empty, it shows everything that I've put inside the range of the search formula.

 

I'd like the results to be empty if the search bar is empty.

 

This is how my formula looks like now:

=FILTER(Contactlist;ISNUMBER(SEARCH(I3;Contactlist[System/funktion]))+ISNUMBER(SEARCH(I3;Contactlist[Namn]))+ISNUMBER(SEARCH(I3;Contactlist[Kommentar]));"Inga resultat")

 

Also, since this is a contactlist, is it possible that the searchresults make the email links clickable? As in if i click the email, it starts up the mailto function pretty much as it would in a normal table.

 

 

 

 

excel

3 Replies

  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Mar 16, 2024

    GustavJ 
    1. "I'd like the results to be empty if the search bar is empty."
    >Try:

     

    =IF(I3="", "", FILTER(Contactlist, ISNUMBER(SEARCH(I3, Contactlist[System/funktion])) + ISNUMBER(SEARCH(I3, Contactlist[Namn])) + ISNUMBER(SEARCH(I3, Contactlist[Kommentar]))))

     

     

    2. "Also, since this is a contactlist, is it possible that the searchresults make the email links clickable? As in if i click the email, it starts up the mailto function pretty much as it would in a normal table."

    > Excel native filtering doesn't directly support hyperlinking, but there's a workaround using a helper column.

    • Add a new column (let’s call it “Email Link”) next to your existing data.

    • In each row of the “Email Link” column, create a hyperlink using the HYPERLINK function. For example:

     

    =HYPERLINK("mailto:" & [@[Email]], [@[Email]])

    Replace the [@Email]] with your cell reference to your email address on the contact list.

     

    • GustavJ's avatar
      GustavJ
      Copper Contributor
      Mar 18, 2024

      Rodrigo_ 

       

      Alright, i think i got both the IF formula to work, as well as the hyperlink (icon in my case).

      Now it looks like this:

      At the moment i have to show the mail icon to every row, even if it does not contain a search result. Can i somehow hide those icons if that specific row does not have a result?

    • GustavJ's avatar
      GustavJ
      Copper Contributor
      Mar 18, 2024

      Hi!

      Thanks for the response! Im not sure why, but the search keeps showing everything even with that string you posted. If i just use =IF(I3="";"";) in an empty cell it returns as "0".

      I will def try out the helper column later today, it looks promising.

       

      EDIT: I think i got the IF formula to work now, no idea what i did, but it works. Thanks!

      With the helper column, could you show me an example of how you think maybe? Im not sure if i understand it correctly.

Resources