1

enter image description here

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

2 Answers 2

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.

1
  • Don't forget to accept whatever answer suits you the most.
    – pjmg
    Aug 31, 2019 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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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