Is it possible to write such a formula that it will substitute in a text value all occurrences of few values with their replacements?
For example I would like to substitute all occurrences of
2. So that text
aaabbbaaa will be changed to
I have prepared an example spreadsheet which also has write access so feel free to add a column with your formula and in the answer just explain it. Also you can add new test cases.
In my “real world” case the look-up table is quite large (45 values) but could be considered “fixed”. So yes, I could just make a formula consisting of 45 nested
SUBSTITUTE but such solution is obviously bad.
Also my real case is not as tricky as the example so there is no risk that replacement text will in any way match (even in parts) any of the values to be substituted. (In the example
ddd is replaced with
a which matches partially value
aaa!) So if that causes problems you might skip the “feature”.
And finally in my real case I'm lucky enough to be able to split the text into parts which contain no more than one value to be replaced (and possibly some other character data!). The result could be then joined back into a single string. If that would make it any easier you could use that as well.
I tried using both
REGEXREPLACE where text to be found and replacement text are arrays of values but that results in an array of results rather than multiple substitutions.
I tried also using
VLOOKUP in replacement text that looked up for
"$1" (the matched text) but that didn't work. The
REGEXREPLACE was treated literally rather than interpreted as matched text (as it would be if it was used in