Google Sheets: How to Substitute Multiple Values


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")

The following screenshot shows how to use this formula in practice:

Notice that each of the substitutions have been made.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Extract Substring in Google Sheets
How to Filter by Date Range in Google Sheets
How to AutoFill Dates in Google Sheets

Featured Posts

3 Replies to “Google Sheets: How to Substitute Multiple Values”

  1. I would suggest that Google Sheet (and MS Excel, for that matter) developers consider adding a formula called “SUBSTITUTES” (notice the plural “s” at the end), then format it in the same way that the “IFS” statement takes in multiple IF conditions without nesting. For instance, instead of :

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,”Point”,”Pt”),”Shooting”,”St”),”Small”,”Sm”)

    …create the following:

    =SUBSTITUTES(A2,”Point”,”Pt”,”Shooting”,”St”,”Small”,”Sm”)

    REGEXREPLACE function may also be considered for this task as well, but the solution retains the same format.

  2. I want to substitute 2 words and also want the formula to be implemented in every entry of google form. Using following and error occurs. What may be the cause.
    =arrayformula(SUBSTITUTE(SUBSTITUTE(S2,”TRUE”,”Already Reserved”)S2,”FALSE”,”Available”)

Leave a Reply

Your email address will not be published. Required fields are marked *