Remove words from a String based on a list

Bebeto Le Garfs 0
Viewed 46

Here's what I need to accomplish. I have a string with several words separated by a comma. Now, I need to remove any word from that string that is part of a list of words in another column.

You can see in the image that the String on Column D. On column E is my expected result, so basically, it removed any words listed in column B.

I've been trying to do that with regex, but I don't know how to tell regex to use a list of words.

Any ideas?

enter image description here

Here's the link for the spreadsheet

google-sheets
1 Answers

Use this formula

=ArrayFormula(IF(D2:D="",,BYROW(REGEXREPLACE( D2:D, TEXTJOIN("|", 1,$B$2:$B), ""), 
                                LAMBDA(v, ArrayFormula(TEXTJOIN( ", ", 1, TRIM(SPLIT(v, ","))))))))

enter image description here

Resources

Make a copy of this example sheet or create your own from the tables down below.

String
mole, dugong, porcupine
ocelot, parakeet, colt
chimpanzee, cougar, mole, parakeet, ocelot
skunk, snake, mole
squirrel, skunk
raccoon, donkey
parakeet, lemur, dugong, giraffe
squirrel, ocelot
polar bear, cougar, lemur, dugong
parakeet, musk-ox, mountain goat, highland cow
raccoon, polar bear, gnu
raccoon, lemur, porcupine, mountain goat
raccoon, giraffe, ocelot
cougar, snake, donkey, porcupine, gnu, chimpanzee
donkey, parakeet, gnu, squirrel, musk-ox, giraffe
porcupine, giraffe
chimpanzee, porcupine
raccoon, highland cow, ocelot, lemur, squirrel
snake, giraffe, chimpanzee, musk-ox, squirrel
porcupine, colt, ocelot, highland cow, skunk

+---------------+-------------------+
|  Words I want | Words I dont want |
+---------------+-------------------+
| lemur         | cougar            |
+---------------+-------------------+
| colt          | highland cow      |
+---------------+-------------------+
| mountain goat | donkey            |
+---------------+-------------------+
| gnu           | snake             |
+---------------+-------------------+
| chimpanzee    | raccoon           |
+---------------+-------------------+
| squirrel      |                   |
+---------------+-------------------+
| skunk         |                   |
+---------------+-------------------+
| mole          |                   |
+---------------+-------------------+
| polar bear    |                   |
+---------------+-------------------+
| giraffe       |                   |
+---------------+-------------------+
| musk-ox       |                   |
+---------------+-------------------+
| dugong        |                   |
+---------------+-------------------+
| ocelot        |                   |
+---------------+-------------------+
| porcupine     |                   |
+---------------+-------------------+
| parakeet      |                   |
+---------------+-------------------+
Osm0
Related