Improved SUBSTITUTE function for replacing multiple values ​​in Google Sheets

by

The SUBSTITUTE function in Google Sheets lets you find and replace a specific text value in a cell with another value. If you need to replace multiple values ​​in a string with different values, you need to nest multiple SUBSTITUTE functions which can be complicated.

Google Sheets offers built-in SUBSTITUTE A function that can find and replace specific text in a cell with another value. For example, you can use =SUBSTITUTE("My favorite color is red", "red", "blue") To replace the text red in the string with blue. The SUBSTITUTE The function is case-sensitive and will replace all occurrences of the search text.

Replace multiple values ​​with SUBSTITUTE

Now consider a scenario where you have to replace multiple values ​​in a string with different values. For example, if you have a template string like My name is {{name}} and I work at {{company}} And you want to replace {{name}} With real name and {{company}} with the company name.

The SUBSTITUTE A function is not useful here because it can only replace one value at a time but you can use nested SUBSTITUTE Functions to replace multiple values ​​in a single cell. There will be one SUBSTITUTE function for each value you want to replace.

Nested SUBSTITUTE functions

=SUBSTITUTE(
  SUBSTITUTE(A1,"{{name}}","Amit"),
   "{{company}}","Digital Inspiration")

Multiple choice function for Google Sheets

The nested approach works, but the formula can be long and complicated when you have to replace multiple values ​​in a single cell. Here is a simple approach to create a custom function using Google Apps Script that can replace multiple values ​​in a single call.

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

The function takes an input string as the first argument followed by a pair of search and replacement values. Each pair contains two values ​​- the first value is the search text and the second value is the replacement text. The function will replace all occurrences of the search text in the input string with the corresponding replacement text.

Open your Google Sheets, go to Extensions > Apps Scripts and paste the following code into the script editor. Save the script and you can use it now MULTI_SUBSTITUTE Actions in your Google Sheet to replace multiple values ​​in a single cell.


function MULTI_SUBSTITUTE(text, ...opts) {
  for (let i = 0; i < opts.length; i += 2) {
    const searchValue = opts(i);
    const replaceValue = opts(i + 1);

    
    const regex = new RegExp(searchValue, 'gi');

    
    text = text.replace(regex, replaceValue || '');
  }
  return text;
}

This custom function uses regular expressions to replace all occurrences of the search value in the input string. The i The flag in regular expressions makes the search case-insensitive, unlike the built-in SUBSTITUTE function.

You can also use the multiple choice function to generate pre-filled links for Google Forms.

Leave a Comment