Search code examples
google-sheetsgoogle-sheets-formula

Using multiple SUBSTITUTE functions dynamically

Let's say I have a list of strings and I want to remove specific words from them. I can easily use multiple SUBSTITUTE functions, for example, this will remove the strings in B2, B3 and B4 from the string in A2: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,$B$2,""),$B$3,""),$B$4,"")

How can I make this dynamic so that when I add more terms to remove in the B column they'll be removed automatically from A2. I tried the following methods but they didn't work:

1 - add the B cells as an array =SUBSTITUTE(A2,{$B$2:$B$4},"") or =SUBSTITUTE(A2,{$B$2,$B$3,$B$4},"")

2 - Make a single condition

cat|donkey|mouse

3 - Using Indirect and concatenate - I built the correct function as a string (using REPT and CONCATENATE) and tried to activate it with INDIRECT) but this also failed.

Here's the spreadsheet (Col A are the strings to clea, B are the words to remove, D is the manual method that works, F, H and K are the failed 3 attempts). https://docs.google.com/spreadsheets/d/15u8qZ0xQkjvTRrJca6AInoQ4aPkijccouAETE4Gyr9I/edit#gid=0

Solution

  • In the 'Copy' of the tab I entered

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

    See if that works for you?

    EXPLANTION

    • LEN(A2:A) basically limits the output to the rows that a value in column A
    • REGEXREPLACE uses a regular expression to replace parts of the string. That regular expression is constructed by the TEXTJOIN function.
    • TEXTJOIN combines the text from the range B2:B, with a specifiable delimiter separating the different texts. Here the pipe character (which means 'or' in regex) is used. The second paramater of this function is set to TRUE (or 1) so that empty cells selected in the text arguments won't be included in the result.

    REFERENCES