Table of Contents
Google Sheets’ SUBSTITUTE function can be used to replace multiple values in a string of text with other values. This is done by providing multiple arguments to the function, with each argument consisting of the value to be replaced and the value to be used as a replacement. This allows you to quickly and efficiently substitute multiple values for others within a single cell or range of cells.
You can use the following formula to substitute multiple values in a cell in Google Sheets:
=SUBSTITUTE(SUBSTITUTE(A1,"oldtext1","newtext1"),"oldtext2","newtext2")
This particular formula substitutes two values in a cell, but you can create as many nested SUBSTITUTE functions as you’d like to substitute as many values as you’d like.
The following examples show how to use this formula in practice.
Example 1: Substitute Two Values in Google Sheets
Suppose we have the following list of basketball positions in Google Sheets:
We can use the following formula to substitute “Guard” with “Gd” and “Forward” with “Fd”:
=SUBSTITUTE(SUBSTITUTE(A2,"Guard","Gd"),"Forward","Fd")
The following screenshot shows how to use this formula in practice:
Notice that the words “Guard” and “Forward” were replaced in each cell with the substitutions that we specified in the formula.
Example 2: Substitute Three Values in Google Sheets
Suppose we have the following list of basketball positions in Google Sheets:
We can use the following formula to substitute “Point” with “Pt”, “Shooting” with “St” and “Small” with “Sm”:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Point","Pt"),"Shooting","St"),"Small","Sm")
Notice that each of the substitutions have been made.