Search code examples
regexgoogle-sheetsgoogle-sheets-formulacase-insensitive

Using Filter function in Google Sheets to filter cels that contain a certain value

I am trying to use the filter function or any related function in order to filter all values that contain that value, but for some reason I am not succeeding yet.

To use an example, in the list below I would like to filter all values that contain banana.
Let's assume that this is Column A row 1-6 in the Google Sheet.

Banana flavor  
Mandarin  
Banana  
Banana boat  
Banana beach  
Peach
Solution

  • EDIT
    Following both your comments:
    "Is it also possible to put them vertical without blank cells?".
    AND
    "it only returns Banana if you enter Banana in my file, not if you enter banana. How can this be fixed?"

    The fix
    To have the formula omit blank cells, wrap in a query and in order to make the regex case insensitive, use (?i).

    =QUERY(
   INDEX(IF(REGEXMATCH(A1:A7,"(?i)Banana")=true,A1:A7,)),
      " where Col1<>'' ")

    regex query case insensitive

    You can even have the value Banana in a cell like D1

    In that case one should use

    =QUERY(
   INDEX(IF(REGEXMATCH(A1:A7,"(?i)"&D1&"")=true,A1:A7,)),
      " where Col1<>'' ")

    Original answer

    Please try

    =INDEX(IF(REGEXMATCH(A1:A7,"Banana")=true,A1:A7,))

    (Do adjust the formula according to your ranges and locale)

    filter using regexmatch