Find and replace in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to replace all occurrences of "apple" with "orange" in cell A1.

Google Sheets

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

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

A"apple"

B"orange"

CA1

D"fruit"

Attempts:

3 left

💡 Hint

Common Mistakes

Using the replacement text instead of the text to find.

Not using quotes around the text to find.

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

2fill in blank

medium

Complete the formula to replace only the first occurrence of "cat" with "dog" in cell B2.

Google Sheets

=SUBSTITUTE(B2, "cat", [1], 1)

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

A1

B"doggy"

C"dog"

D"cat"

Attempts:

3 left

💡 Hint

Common Mistakes

Replacing with the wrong text.

Confusing the position argument with the replacement text.

The third argument is the replacement text. Here, we replace the first "cat" with "dog".

3fill in blank

hard

Fix the error in the formula to replace "blue" with "red" in cell C3.

Google Sheets

=SUBSTITUTE(C3, [1], "red")

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

A"blue"

Bblue

CC3

Dred

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around the text to find.

Using a cell reference instead of text.

The text to find must be in quotes. Without quotes, it is treated as a cell or name, causing an error.

4fill in blank

hard

Fill both blanks to replace only the second occurrence of "cat" with "dog" in cell D4.

Google Sheets

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

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

A"cat"

B"dog"

C"doggy"

D"catty"

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the find and replace texts.

Using incorrect quotes or missing them.

The first blank is the text to find, "cat". The second blank is the replacement text, "dog". The last argument 2 means only the second occurrence is replaced.

5fill in blank

hard

Fill all three blanks to replace "red" with "green" in cell E5, but only for the third occurrence.

Google Sheets

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

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

A"red"

B"green"

C3

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Not putting text values in quotes.

Using the wrong occurrence number.

The first blank is the text to find "red". The second blank is the replacement text "green". The third blank is the occurrence number 3, meaning only the third "red" is replaced.