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.

Practice

1. Which function would you use in Excel to replace all occurrences of the word apple with orange in a text string?

easy

A. REPLACE

B. SUBSTITUTE

C. CONCATENATE

D. LEFT

Solution

1. Step 1: Understand the function purpose

   SUBSTITUTE replaces specific text by matching the exact word or phrase in the string.

2. Step 2: Compare with REPLACE

   REPLACE changes text based on position, not matching text, so it is not suitable here.

3. Final Answer:

   SUBSTITUTE -> Option B

4. Quick Check:

   Replace by matching text = SUBSTITUTE [OK]

Hint: Use SUBSTITUTE to replace specific words by matching text [OK]

Common Mistakes:

• Using REPLACE when you need to match text
• Confusing SUBSTITUTE with CONCATENATE
• Trying to use LEFT to replace text

2. Which of the following is the correct syntax to replace the first 3 characters in cell A1 with XYZ using the REPLACE function?

easy

A. REPLACE("XYZ", 1, 3, A1)

B. REPLACE(A1, "XYZ", 1, 3)

C. REPLACE(A1, 3, 1, "XYZ")

D. REPLACE(A1, 1, 3, "XYZ")

Solution

1. Step 1: Recall REPLACE syntax

   REPLACE(text, start_num, num_chars, new_text) replaces characters starting at start_num for num_chars length.

2. Step 2: Match syntax to question

   To replace first 3 characters in A1 with "XYZ", start_num=1, num_chars=3, new_text="XYZ".

3. Final Answer:

   REPLACE(A1, 1, 3, "XYZ") -> Option D

4. Quick Check:

   REPLACE(text, 1, 3, "XYZ") correct syntax [OK]

Hint: REPLACE(text, start, length, new_text) replaces by position [OK]

Common Mistakes:

• Swapping start_num and num_chars
• Putting new_text in wrong position
• Using SUBSTITUTE syntax for REPLACE

3. What will be the result of the formula =SUBSTITUTE("banana", "a", "o", 2)?

medium

A. "bonana"

B. "banana"

C. "banona"

D. "bonono"

Solution

1. Step 1: Understand SUBSTITUTE with instance number

   SUBSTITUTE(text, old_text, new_text, instance_num) replaces only the specified occurrence.

2. Step 2: Apply to "banana" replacing 2nd "a" with "o"

   "banana" has 'a' at positions 2, 4, and 6. The 2nd 'a' is at position 4, replaced by 'o' gives "banona".

3. Final Answer:

   "banona" -> Option C

4. Quick Check:

   Replace 2nd 'a' with 'o' = "banona" [OK]

Hint: SUBSTITUTE with instance replaces only that occurrence [OK]

Common Mistakes:

• Replacing all 'a's instead of just the 2nd
• Confusing instance number with position
• Expecting REPLACE to work by matching text

4. The formula =REPLACE(A1, 2, 3, "XYZ") returns an error. Which is the most likely cause?

medium

A. The start position 2 is greater than the length of text in A1

B. Cell A1 is empty

C. The number of characters to replace is negative

D. The new text "XYZ" is too long

Solution

1. Step 1: Check REPLACE parameters

   REPLACE(text, start_num, num_chars, new_text) requires start_num within text length.

2. Step 2: Identify error cause

   If start_num (2) is greater than text length, REPLACE cannot start and returns error.

3. Final Answer:

   The start position 2 is greater than the length of text in A1 -> Option A

4. Quick Check:

   Start position must be within text length [OK]

Hint: Start position must be inside text length for REPLACE [OK]

Common Mistakes:

• Assuming empty cell causes error
• Thinking new text length matters
• Using negative number of characters

5. You have the text "Invoice12345" in cell A1. Which formula will replace the numeric part with "67890" regardless of how many digits the number has?

hard

A. REPLACE(A1, 8, LEN(A1)-7, "67890")

B. SUBSTITUTE(A1, "12345", "67890")

C. REPLACE(A1, 8, 5, "67890")

D. SUBSTITUTE(A1, MID(A1,8,5), "67890")

Solution

1. Step 1: Identify numeric part position and length

   The text "Invoice12345" has "Invoice" (7 chars) then numbers starting at position 8. Length of numbers is LEN(A1)-7.

2. Step 2: Use REPLACE with dynamic length

   REPLACE(A1, 8, LEN(A1)-7, "67890") replaces from position 8 for all digits, no matter how many.

3. Final Answer:

   REPLACE(A1, 8, LEN(A1)-7, "67890") -> Option A

4. Quick Check:

   Dynamic length replace = REPLACE with LEN() [OK]

Hint: Use LEN() to replace variable-length text with REPLACE [OK]

Common Mistakes:

• Hardcoding length instead of using LEN()
• Using SUBSTITUTE which needs exact text match
• Using MID inside SUBSTITUTE incorrectly