CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Tom
Tom

Reputation: 233

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

Upvotes: 0

Views: 3108

Answers (3)

Hkscsheph
Hkscsheph

Reputation: 1

By using some newly introduced Google Sheet Formula, I have made myself a more generic formula

=MAP(
  targetRange, 
  LAMBDA(
    input, 
    REDUCE(
      input, 
      SPLIT("from1>to1%from2>to2%from3>to3","%"), 
      LAMBDA(input, regex, 
        REGEXREPLACE(
          input, 
          REGEXEXTRACT(regex,"(.*)>"), 
          REGEXEXTRACT(regex, ">(.*)")
) ) ) ) )

The part SPLIT("from1>to1%from2>to2%from3>to3","%") could also be replaced dynamically as

BYROW(
  subsTableRange,
  LAMBDA(r, TEXTJOIN(">", 1, r)
)

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36890

You can also try-

=TEXTJOIN(" ",TRUE,FILTER(SPLIT(A2," "),ISERROR(MATCH(SPLIT(A2," "),$B$2:$B$7,0))))

Upvotes: 0

JPV
JPV

Reputation: 27282

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

Upvotes: 3

Related Questions