Google Sheets - filter multiple columns if contains text-CodePudding

I want to filter rows that contain a word in any column (among other text, not an exact match).

E.G.: I'd like to filter any line where the word "blue" appears in any column

CodePudding user response：

you can try:

=QUERY((BYROW(A2:C, LAMBDA(acx,IF(REGEXMATCH(TEXTJOIN("|",1,INDEX(SUBSTITUTE(acx," ","|"))),"blue"),acx,)))),"Select * Where Col1!=''")

CodePudding user response：

You can use this, it should work with as many column as you have. Let me know!

=FILTER(A:C,BYROW(A:C,LAMBDA(each, REGEXMATCH (CONCATENATE(each),"blue"))))