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

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

CodePudding user response：

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<>'' ")

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)