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

user_16544457 0
Viewed 51

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
google-sheets-formula google-sheets case-insensitive regex
1 Answers

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

user_15277800
Related