Forum Discussion

Anonymous's avatar
Anonymous
Jul 25, 2019

How can I insert a search box into a spreadsheet that searches comma separated values in cells?

......

excel

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 26, 2019

    Deleted 

    For such sample

    you may use B1 to enter terms, in B2 add the formula

    =IFERROR(INDEX($A$11:$A$14,AGGREGATE(15,6,1/ISNUMBER(SEARCH($B$1,$B$11:$B$14))*(ROW($B$11:$B$14)-ROW($B$10)),ROW()-ROW($B$1))),"")

    and drag it down.

    Please see attached.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 26, 2019

        Deleted ,

        The workaround could be to wrap keywords by spaces and search for " apple ", not "apple", like

        =IFERROR(
  INDEX($A$11:$A$14,
    AGGREGATE(15,6,
      1/ISNUMBER(SEARCH(" " & $B$1 & " "," " & SUBSTITUTE($B$11:$B$14,","," ")))*
        (ROW($B$11:$B$14)-ROW($B$10)),
      ROW()-ROW($B$1)
  )
  ),
"")

        If there are spaces within your keywords you may use any other character instead of space. Please check attached.

  • nabilmourad's avatar
    nabilmourad
    MVP
    Jul 25, 2019

    Deleted 

    Hi

    The function used to search for a keyword is called "Search"
    If you type "Apple" as a keyword in Cell B1 and you want to search in the Range A5:A50 for the existence of this keyword,

    You can type IsNumber(Search($B$1,A1)) >> and copy it all the way down >> it will return a TRUE if the keyword exists and a FALSE if the keyword does not exist

    Using this concept with an advanced filter enables you to extract the record having the keyword.

    If you have multiple keywords then, you need to include your function in an OR function and put the keywords in separate cells.

    Should you wish a more customized answer then post an Excel spreadsheet with sample data.

    Thanks

    Nabil Mourad

Resources