Google Spreadsheets: Any Regex Replace

Question

I currently use

SUBSTITUTE(text, search_text, new text, occurrence)

to replace text, but I am starting to find that I have a list of things to replace, the above method will become something like

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(...)...)...)...

very messy, whats a better method of making this work?

Answer 1

You might be able to use a regular expression using the REGEXREPLACE(text, regular_expression, replacement) function instead.

If this doesn't work, it's possible to write custom functions using Google Apps Script. Maybe you could write one that can accept multiple strings to be replaced.

Edit: I just wrote such a function:

function SubstituteAll(text) {
  arguments = Array.prototype.slice.apply(arguments);
  arguments.shift();
  var replacement = arguments.pop();
  for(var i=0; i<arguments.length; i++)
  {
    text = text.replace(arguments[i], replacement);
  }
  return text;
}

Usage:

SubstituteAll(text, search_text1, search_text2, ..., replacement_text)