Find and replace in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

Excel

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

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

A"fruit"

B"apple"

CA1

D"orange"

Attempts:

3 left

💡 Hint

Common Mistakes

Using the replacement text instead of the text to find.

Not putting the text inside quotes.

The SUBSTITUTE function replaces all occurrences of the text given in the second argument. 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.

Excel

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

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

A1

B"cat"

C"dog"

D"doggy"

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the number 1 as the replacement text.

Confusing the order of arguments.

The third argument is the replacement text. To replace the first occurrence only, we specify 1 as the fourth argument.

3fill in blank

hard

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

Excel

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

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

Ablue

BC3

C"red"

D"blue"

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving the text without quotes.

Using the replacement text instead of the text to find.

The text to find must be a string inside quotes. Without quotes, Excel treats it as a name or reference, causing an error.

4fill in blank

hard

Fill both blanks to replace all occurrences of "cat" with "dog" in cell D4 and make the result uppercase.

Excel

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

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

A"cat"

B"dog"

C"Cat"

D"Dog"

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing uppercase and lowercase in the text to find.

Not putting texts inside quotes.

SUBSTITUTE replaces "cat" with "dog". UPPER converts the whole result to uppercase letters.

5fill in blank

hard

Fill all three blanks to replace "2023" with "2024" in cell E5, then trim extra spaces, and finally convert to proper case.

Excel

=PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(E5, [1], [2]), [3], " ")))

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

A"2023"

B"2024"

C" "

D"2025"

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong text for trimming spaces.

Not nesting functions correctly.

SUBSTITUTE replaces "2023" with "2024". TRIM removes extra spaces (like double spaces " "). PROPER capitalizes the first letter of each word.