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

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory

0

Awards & Titles

Digital Inspiration has won several awards since it's launch in 2004.

Google Developer Expert

Google Developer Expert

Google awarded us the Google Developer Expert award recogizing our work in Google Workspace.

ProductHunt Golden Kitty

ProductHunt Golden Kitty

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

Microsoft MVP Alumni

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional (MVP) title for 5 years in a row.

Google Cloud Champion

Google Cloud Champion

Google awarded us the Champion Innovator title recognizing our technical skill and expertise.

Email Newsletter

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

We will never send any spam emails. Promise.