SUBSTITUTE and REPLACE in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

Excel

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

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

A"dog"

B"cat"

CA1

D"replace"

Attempts:

3 left

💡 Hint

Common Mistakes

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

Using the cell reference instead of the text to find.

The SUBSTITUTE function replaces all occurrences of the text you specify. Here, you 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.

Excel

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

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

A"orange"

B"apple"

C2

D"replace"

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the occurrence number in the wrong place.

Using the text to find as the replacement text.

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

3fill in blank

hard

Fix the error in the formula to replace the first 3 characters in cell C3 with "XYZ".

Excel

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

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

A3

B1

C"XYZ"

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 instead of 3 for the number of characters.

Using 0 which means no characters replaced.

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

4fill in blank

hard

Fill both blanks to create a formula that replaces the 4 characters starting at position 2 in cell D4 with "test".

Excel

=REPLACE(D4, [1], [2], "test")

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

A2

B3

C4

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping start position and number of characters.

Using wrong numbers that don't match the instruction.

The second argument is the start position (2), and the third argument is the number of characters to replace (4).

5fill in blank

hard

Fill all three blanks to create a formula that substitutes "red" with "blue" only in the third occurrence in cell E5, then replaces the first 2 characters of the result with "Hi".

Excel

=REPLACE(SUBSTITUTE(E5, [1], [2], [3]), 1, 2, "Hi")

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

A"red"

B"blue"

C3

D"Hi"

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the order of SUBSTITUTE arguments.

Using wrong occurrence number.

Confusing REPLACE arguments.

The SUBSTITUTE replaces the third occurrence of "red" with "blue". Then REPLACE changes the first 2 characters to "Hi".