Multiple substitutions in a single text

Question

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 aaa with 1 and bbb with 2. So that text aaabbbaaa will be changed to 121.

---

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 SUBSTITUTE and 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 REGEXREPLACE with VLOOKUP in replacement text that looked up for "$1" (the matched text) but that didn't work. The "$1" within VLOOKUP within REGEXREPLACE was treated literally rather than interpreted as matched text (as it would be if it was used in REGEXREPLACE directly).

Answer 1

As discussed in the thread you linked to in comments, the formula solution is quite convoluted and potentially very slow, if there are multiple instances to be replaced in the string. However with your qualification:

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 believe the solution becomes a little more straight forward (and reasonably efficient, I would say):

=IFERROR(REGEXREPLACE(A2;JOIN("|";Lookup!$A$2:$A$5);VLOOKUP(REGEXEXTRACT(A2;JOIN("|";Lookup!$A$2:$A$5));Lookup!$A$2:$B$5;2;0)&"");A2)

which has the added advantage of being able to be easily converted into an array formula that will populate down the column:

=ArrayFormula(IFERROR(REGEXREPLACE(A2:A;JOIN("|";Lookup!A2:A5);VLOOKUP(REGEXEXTRACT(A2:A;JOIN("|";Lookup!A2:A5));Lookup!A2:B5;2*SIGN(ROW(A2:A));0)&"");A2:A))

I have also entered a custom function solution:

function MSUBSTITUTE(text, subTable)
{
  var searchArray = [], subArray = [];
  for (var i = 0, length = subTable.length; i < length; i++)
  {
    if (subTable[i][0])
    {
      searchArray.push(subTable[i][0]);
      subArray.push(subTable[i][1]);
    }
  }
  var re = new RegExp(searchArray.join('|'), 'g');
  return text.replace(re, function (match) {return subArray[searchArray.indexOf(match)];});
}

=MSUBSTITUTE(A2;Lookup!$A$2:$B$5)

It would be fairly trivial to convert this to an auto-populating array formula, as well.

Answer 2

This custom function can be reduced to:

function multipass(text, ChangeFrom, ChangeTo) {
  ChangeFrom = ChangeFrom.map (String);
  var re = new RegExp(ChangeFrom.join('|'), 'g');
  return text.replace(re, function (match) {return ChangeTo[ChangeFrom.indexOf(match)];});
}

The key thing is ChangeFrom.map (String) which is necessary because although the range is converted to an array, its elements are not converted to Strings. It's essential to use String not toString in this line or the indexOf will fail.

For example:

=multipass(D5, Changes!A1:A10, Changes!B1:B10)

In this example, the result will be whatever was in D5, with substitutions made from the 2 columns Changes!A1:A10 and Changes!B1:B10. In practice these would be named columns, but have used references to make it clearer.

(I called it multipass in honour of 5th Element :)