SUBSTITUTE and REPLACE in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to replace all occurrences of "cat" with "dog" in cell A1.

Google Sheets

=SUBSTITUTE(A1, [1], "dog")

Drag options to blanks, or click blank then click option'

A"cat"

B"dog"

CA1

D"replace"

Attempts:

3 left

💡 Hint

Common Mistakes

Using the replacement text "dog" as the text to find.

Not putting the text to find in quotes.

The SUBSTITUTE function replaces all occurrences of the text you specify. Here, we want to replace "cat" with "dog".

2fill in blank

medium

Complete the formula to replace only the second occurrence of "apple" with "orange" in cell B2.

Google Sheets

=SUBSTITUTE(B2, "apple", [1], 2)

Drag options to blanks, or click blank then click option'

A"replace"

B"apple"

C2

D"orange"

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the occurrence number as the replacement text.

Confusing the order of arguments.

The third argument in SUBSTITUTE is the replacement text. To replace the second occurrence, we specify 2 as the fourth argument.

3fill in blank

hard

Fix the error in this formula to replace 3 characters starting at position 5 in cell C3 with "XYZ".

Google Sheets

=REPLACE(C3, 5, [1], "XYZ")

Drag options to blanks, or click blank then click option'

A2

B3

C4

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Replacing too few or too many characters.

Confusing the position and length arguments.

The REPLACE function replaces a number of characters starting at a position. Here, we want to replace 3 characters starting at position 5.

4fill in blank

hard

Fill both blanks to create a formula that replaces the first occurrence of "red" with "blue" in cell D4.

Google Sheets

=SUBSTITUTE(D4, [1], [2], 1)

Drag options to blanks, or click blank then click option'

A"red"

B"blue"

C"green"

D"yellow"

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the find and replacement texts.

Not specifying the occurrence number.

The SUBSTITUTE function replaces text. The second argument is the text to find, the third is the replacement, and the fourth is which occurrence to replace.

5fill in blank

hard

Fill all three blanks to create a formula that replaces 4 characters starting at position 3 in cell E5 with the text "done".

Google Sheets

=REPLACE(E5, [1], [2], [3])

Drag options to blanks, or click blank then click option'

A3

B4

C"done"

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong numbers for position or length.

Not putting the replacement text in quotes.

The REPLACE function needs the start position, number of characters to replace, and the new text. Here, start at 3, replace 4 characters, with "done".