1

enter image description here

I have a list in column A and I want to remove any cells that contain any word.

1

In C2 enter this formula :

=filter(
     ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A)),
      ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A))<>""
)

Breakdown of the formula :

   =REGEXMATCH(A2,TEXTJOIN("|",true,B2:B))

Regexmatch returns TRUE or FALSE if A2 contains any of the words in column B. TEXJOIN is self-explanatory, it helps creating a small regex like this one : value1|value2|value3.

 =ARRAYFORMULA(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)))     

Arrayformula is a way to test the range A2:A.

ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A))

Simple IF : if true do nothing, if false, copy the value.

Last function is a FILTER to eliminate every blank entries.
<>"" is a check for empty values.

| improve this answer | |
  • Awesome! Thank you! – Michael Rock Aug 30 '19 at 23:10
  • Don't forget to accept whatever answer suits you the most. – pjmg Aug 31 '19 at 11:05
1

=FILTER(A2:A, NOT(REGEXMATCH(A2:A, TEXTJOIN("|", 1, B2:B))))

0


=ARRAYFORMULA(FILTER(
 IFERROR(REGEXEXTRACT(A2:A, TEXTJOIN("|", 1, B2:B)), A2:A), NOT(COUNTIF(B2:B, 
 IFERROR(REGEXEXTRACT(A2:A, TEXTJOIN("|", 1, B2:B)), A2:A)))))

0

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.