SUBSTITUTE and REPLACE in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this SUBSTITUTE formula?

Given cell A1 contains the text "apple banana apple", what will be the result of the formula =SUBSTITUTE(A1, "apple", "orange", 2)?

Google Sheets

=SUBSTITUTE(A1, "apple", "orange", 2)

A "orange banana orange"

B "orange banana apple"

C "apple banana orange"

D "orange banana apple apple"

💡 Hint

The number 2 means only the second occurrence of "apple" is replaced.

SUBSTITUTE replaces only the specified occurrence when the fourth argument is given. Here, only the second "apple" is replaced with "orange".

📊 Formula Result

intermediate

What does this REPLACE formula return?

If cell B1 contains "1234567890", what is the result of =REPLACE(B1, 4, 3, "ABC")?

Google Sheets

=REPLACE(B1, 4, 3, "ABC")

A "123ABC7890"

B "1234ABC7890"

C "1234567ABC0"

D "123ABC4567890"

💡 Hint

REPLACE starts at position 4 and replaces 3 characters with "ABC".

Starting at the 4th character, it removes 3 characters (4,5,6) and inserts "ABC" instead.

❓ Function Choice

advanced

Which formula replaces only the first occurrence of "cat" with "dog" in cell C1?

Cell C1 contains the text "cat cat cat". Which formula will change only the first "cat" to "dog"?

A =SUBSTITUTE(C1, "cat", "dog", 1)

B =REPLACE(C1, FIND("cat", C1), 3, "dog")

C =SUBSTITUTE(C1, "cat", "dog")

D =REPLACE(C1, 1, 3, "dog")

💡 Hint

SUBSTITUTE with the 4th argument controls which occurrence to replace.

SUBSTITUTE with occurrence 1 replaces only the first "cat". REPLACE needs position and length but option A replaces only the first occurrence too, but option A is clearer and safer.

🎯 Scenario

advanced

You want to replace the 3rd word in a sentence with "blue". Which formula is best?

Cell D1 contains "red green yellow black white". You want to replace the 3rd word "yellow" with "blue" using a formula. Which approach works best?

A =SUBSTITUTE(D1, "yellow", "blue", 3)

B =REPLACE(D1, FIND("yellow", D1), LEN("yellow"), "blue")

C =SUBSTITUTE(D1, "yellow", "blue")

D =REPLACE(D1, 12, 6, "blue")

💡 Hint

REPLACE needs the exact position and length of the text to replace.

Option B finds the position of "yellow" and replaces exactly that word with "blue". SUBSTITUTE with occurrence 3 won't work because "yellow" appears only once.

❓ data_analysis

expert

How many replacements occur with this formula?

Cell E1 contains "one two one two one two". What is the number of replacements made by =SUBSTITUTE(E1, "one", "three")?

Google Sheets

=SUBSTITUTE(E1, "one", "three")

A 0

B 1

C 2

D 3

💡 Hint

Without the 4th argument, SUBSTITUTE replaces all occurrences.

SUBSTITUTE replaces all occurrences of "one" with "three". There are 3 "one" words, so 3 replacements happen.