SUBSTITUTE and REPLACE in Excel - 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 is the result of the formula =SUBSTITUTE(A1, "apple", "orange", 2)?

Excel

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

A "orange banana apple"

B "apple banana orange"

C "orange banana orange"

D "orange banana apple apple"

💡 Hint

The 4th argument in SUBSTITUTE tells which occurrence to replace.

SUBSTITUTE replaces only the specified occurrence. Here, only the 2nd 'apple' is replaced with 'orange'. The first 'apple' remains unchanged.

📊 Formula Result

intermediate

What does this REPLACE formula return?

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

Excel

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

A "1234567ABC0"

B "1234ABC890"

C "123ABC7890"

D "ABC4567890"

💡 Hint

REPLACE starts replacing at the position given by the 2nd argument and replaces the number of characters given by the 3rd argument.

Starting at character 4, it replaces 3 characters (4th, 5th, 6th) with "ABC". So "1234567890" becomes "123ABC7890".

❓ Function Choice

advanced

Which formula correctly replaces only the 3rd occurrence of "cat" with "dog" in cell C3?

Cell C3 contains the text "cat dog cat cat dog cat". Which formula will replace only the 3rd "cat" with "dog"?

A =SUBSTITUTE(C3, "cat", "dog", 3)

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

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

D =REPLACE(C3, 3, 3, "dog")

💡 Hint

SUBSTITUTE has an optional 4th argument to specify which occurrence to replace.

Option A uses SUBSTITUTE with the 4th argument set to 3, so it replaces only the 3rd occurrence of "cat" with "dog". Other options either replace all or use incorrect REPLACE syntax.

🎯 Scenario

advanced

You want to replace the first 5 characters of a text in D4 with "Hello". Which formula achieves this?

Cell D4 contains a long text string. You want to replace the first 5 characters with the word "Hello". Which formula will do this correctly?

A =SUBSTITUTE(D4, "Hello", LEFT(D4,5))

B =SUBSTITUTE(D4, LEFT(D4,5), "Hello")

C =REPLACE(D4, 5, 1, "Hello")

D =REPLACE(D4, 1, 5, "Hello")

💡 Hint

REPLACE lets you specify the start position and number of characters to replace.

Option D replaces starting at position 1, replacing 5 characters with "Hello". Option D replaces all occurrences of the first 5 characters, which may be multiple. Options C and D are incorrect uses.

❓ data_analysis

expert

How many characters will remain after this formula?

Cell E5 contains the text "Spreadsheet formulas are fun!". What is the length of the text after applying =REPLACE(E5, 12, 8, "awesome")?

Excel

=REPLACE(E5, 12, 8, "awesome")

A 28

B 29

C 26

D 27

💡 Hint

Count original length, then subtract replaced characters and add new characters.

Original text length is 29. REPLACE removes 8 characters starting at position 12 (space after "Spreadsheet" and "formula") and inserts 7 characters ("awesome"). So new length = 29 - 8 + 7 = 28.