Search in a list and replace from a list but with exact match-CodePudding

In Sheet1 A1:A I have a list of words. In Sheet2 A1:A I have a list with search terms. I now want to match each cell in Sheet1 to see if it contains words from the list of search terms. If yes, then the the words that matches inside the cell should be removed. I have tried the following formulas, unfortunately without success.

=ArrayFormula(regexreplace(A1:A;Sheet2!A:A;""))
=substitute(A1;Sheet2!A$1:A;"")

The formula mentioned in the post Using multiple SUBSTITUTE functions dynamically does not working for me because I need an exact match regexreplace.

=ArrayFormula(IF(LEN(A2:A), REGEXREPLACE(A2:A, TEXTJOIN("|", 1, B2:B),),))

This will also remove the words from words that are part of words. for example, not only the word apple will be deleted, but also the word apple from appletree, which is not supposed to be.

CodePudding user response：

Maybe try

=ArrayFormula(IF(LEN(A2:A), REGEXREPLACE(A2:A, "\b("&TEXTJOIN("|", 1, B2:B)&")\b",),))

and see if that helps?