Google Sheets Formula Remove comma delimited list from string

1
votes

I have prepared the following google sheet: https://docs.google.com/spreadsheets/d/1OU_aLgaIdDD2-pBNSV0y1d5B-YDAZeKbWb1PVtdkGZA/edit?usp=sharing

In cell B3, I want to show the contents of B1, after having removed any value that is displayed in B4. (to make it easier, I included a list of the actual values in B6+). If it's easier, you can use the range B6:B as values to exclude from B1.

I thought about using a nested substitution, however, the list will likely expand over time, so I'm trying to make it more scalable now.

I also looked at https://infoinspired.com/google-docs/spreadsheet/replace-multiple-comma-separated-values-in-google-sheets/ but I couldn't make the bottom formula work.

Because of the nature of this project, it needs to be a formula that I can drag and drop onto more cells (C3:GQ3)

1
regexgoogle-sheetssubstitutiongoogle-sheets-formulatextjoin

1 Answers

1
votes

paste in B3 and drag to the right:

=TEXTJOIN(",", 1, SPLIT(REGEXREPLACE(B$1, SUBSTITUTE(B4, ",", "|"), ), ","))

0