An Improved SUBSTITUTE Perform for Changing A number of Values in Google Sheets

The SUBSTITUTE operate in Google Sheets permits you to discover and exchange a selected textual content worth in a cell with one other worth. If that you must exchange a number of values in a string with completely different values, you may must nest a number of SUBSTITUTE capabilities which may be cumbersome.

Google Sheets provides a built-in SUBSTITUTE operate that may can discover and exchange a selected textual content in a cell with one other worth. As an illustration, you should utilize =SUBSTITUTE("My favourite coloration is pink", "pink", "blue") to switch the textual content pink within the string with blue. The SUBSTITUTE operate is case-sensitive and can exchange all occurrences of the search textual content.

Change A number of Values with SUBSTITUTE

Now contemplate a situation the place it’s important to exchange a number of values in a string with completely different values. As an illustration, when you’ve got a template string like My title is {{title}} and I work at {{firm}} and also you need to exchange {{title}} with the precise title and {{firm}} with the corporate title.

The SUBSTITUTE operate will not be useful right here as a result of it may possibly solely exchange one worth at a time however you should utilize nested SUBSTITUTE capabilities to switch a number of values in a single cell. There can be one SUBSTITUTE operate for every worth that you just need to exchange.

Nested SUBSTITUTE Features

=SUBSTITUTE(
  SUBSTITUTE(A1,"{{title}}","Amit"),
   "{{firm}}","Digital Inspiration")

A number of Substitute Perform for Google Sheets

The nested method works, however the components can get lengthy and sophisticated when it’s important to exchange a number of values in a single cell. Right here’s a less complicated method that makes use of Google Apps Script to create a customized operate that may exchange a number of values in a single name.

=MULTI_SUBSTITUTE(A1, "replace_1", "value_1", "replace_2", "value_2", ... "replace_n", "value_n")

The operate takes the enter string as the primary argument after which pairs of search and exchange values. Every pair has two values – the primary worth is the search textual content and the second worth is the alternative textual content. The operate will exchange all occurrences of the search textual content within the enter string with the corresponding alternative textual content.

Open your Google Sheet, go to Extensions > Apps Script and paste the next code within the script editor. Save the script and now you can use the MULTI_SUBSTITUTE operate in your Google Sheet to switch a number of values in a single cell.

operate MULTI_SUBSTITUTE(textual content, ...opts) {
  for (let i = 0; i < opts.size; i += 2)  '');
  
  return textual content;
}

This practice operate makes use of common expressions to switch all occurrences of the search worth within the enter string. The i flag within the common expression makes the search case-insensitive in contrast to the built-in SUBSTITUTE operate.

You may also use the a number of substitute operate to generate pre-filled hyperlinks for Google Varieties.