An Improved SUBSTITUTE Function for Replacing Multiple Values in Google Sheets

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'll need to nest multiple SUBSTITUTE functions which can be cumbersome.

Google Sheets offers a built-in SUBSTITUTE function that can can find and replace a specific text in a cell with another value. For instance, you can use =SUBSTITUTE("My favorite color is red", "red", "blue") to replace the text red in the string with blue. The SUBSTITUTE 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 instance, if you have a template string like My name is {{name}} and I work at {{company}} and you want to replace {{name}} with the actual name and {{company}} with the company name.

The SUBSTITUTE function is not helpful 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 would be one SUBSTITUTE function for each value that you want to replace.

Nested Substitute Function

Nested SUBSTITUTE Functions

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

Multiple Substitute Function for Google Sheets

The nested approach works, but the formula can get long and complex when you have to replace multiple values in a single cell. Here’s a simpler approach that uses Google Apps Script to create a custom function 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 the input string as the first argument and then pairs of search and replace values. Each pair has 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.

Multiple Substitute in a Single Cell

Open your Google Sheet, go to Extensions > Apps Script and paste the following code in the script editor. Save the script and you can now use the MULTI_SUBSTITUTE function in your Google Sheet to replace multiple values in a single cell.

/**
 * Replaces multiple occurrences of search text in a string with new values.
 * @returns {string} The modified string with replacements made.
 *
 * @customfunction
 */
function MULTI_SUBSTITUTE(text, ...opts) {
  for (let i = 0; i < opts.length; i += 2) {
    const searchValue = opts[i];
    const replaceValue = opts[i + 1];

    // Regex for case-insensitive search (flags 'gi')
    const regex = new RegExp(searchValue, "gi");

    // Replace all occurrences of the search value
    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 flag in the regular expression makes the search case-insensitive unlike the built-in SUBSTITUTE function.

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

Amit Agarwal is a web geek, solo entrepreneur and loves making things on the Internet. Google recently awarded him the Google Developer Expert and Google Cloud Champion title for his work on Google Workspace and Google Apps Script.

Awards & Recognition

Google Developer Expert

Google Developer Expert

Google awarded us the Developer Expert title recogizing our work in Workspace

ProductHunt Golden Kitty

ProductHunt Golden Kitty

Our Gmail tool won the Lifehack of the Year award at ProductHunt Golden Kitty Awards

Microsoft MVP Alumni

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional title for 5 years in a row

Google Cloud Champion

Google Cloud Champion

Google awarded us the Champion Innovator award for technical expertise

Want to stay up to date?
Sign up for our email newsletter.

We will never send any spam emails. Promise 🫶🏻