SUBSTITUTE and REPLACE in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

Google Sheets

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

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

A"cat"

B"dog"

CA1

D"replace"

Attempts:

3 left

💡 Hint

Common Mistakes

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

Not putting the text to find in quotes.

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

Google Sheets

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

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

A"replace"

B"apple"

C2

D"orange"

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the occurrence number as the replacement text.

Confusing the order of arguments.

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

3fill in blank

hard

Fix the error in this formula to replace 3 characters starting at position 5 in cell C3 with "XYZ".

Google Sheets

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

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

A2

B3

C4

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Replacing too few or too many characters.

Confusing the position and length arguments.

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

4fill in blank

hard

Fill both blanks to create a formula that replaces the first occurrence of "red" with "blue" in cell D4.

Google Sheets

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

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

A"red"

B"blue"

C"green"

D"yellow"

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the find and replacement texts.

Not specifying the occurrence number.

The SUBSTITUTE function replaces text. The second argument is the text to find, the third is the replacement, and the fourth is which occurrence to replace.

5fill in blank

hard

Fill all three blanks to create a formula that replaces 4 characters starting at position 3 in cell E5 with the text "done".

Google Sheets

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

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

A3

B4

C"done"

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong numbers for position or length.

Not putting the replacement text in quotes.

The REPLACE function needs the start position, number of characters to replace, and the new text. Here, start at 3, replace 4 characters, with "done".

Practice

1. What does the SUBSTITUTE function do in Google Sheets?

easy

A. It formats cells with colors.

B. It changes text based on position and length.

C. It adds numbers together.

D. It replaces specific text by matching exact words or letters.

Solution

1. Step 1: Understand SUBSTITUTE purpose

   The SUBSTITUTE function looks for specific text and replaces it exactly where it matches.

2. Step 2: Compare with REPLACE

   Unlike REPLACE, SUBSTITUTE does not use position but matches text content.

3. Final Answer:

   It replaces specific text by matching exact words or letters. -> Option D

4. Quick Check:

   SUBSTITUTE = Replace by matching text [OK]

Hint: SUBSTITUTE changes text by matching words, not positions [OK]

Common Mistakes:

• Confusing SUBSTITUTE with REPLACE
• Thinking SUBSTITUTE works by position
• Assuming SUBSTITUTE changes numbers

2. Which of these is the correct syntax for the REPLACE function in Google Sheets?

easy

A. REPLACE(text, start_num, num_chars, new_text)

B. REPLACE(text, old_text, new_text)

C. REPLACE(old_text, new_text, instance_num)

D. REPLACE(text, new_text, start_num)

Solution

1. Step 1: Recall REPLACE syntax

   REPLACE requires the original text, the start position, number of characters to replace, and the new text.

2. Step 2: Check options

   Only REPLACE(text, start_num, num_chars, new_text) matches this order and parameters correctly.

3. Final Answer:

   REPLACE(text, start_num, num_chars, new_text) -> Option A

4. Quick Check:

   REPLACE syntax = text, start, length, new text [OK]

Hint: REPLACE needs position and length, SUBSTITUTE needs old text [OK]

Common Mistakes:

• Mixing parameter order
• Using old_text instead of start_num
• Omitting number of characters to replace

3. Given cell A1 contains the text "apple banana apple", what is the result of =SUBSTITUTE(A1, "apple", "orange", 2)?

medium

A. "orange banana orange"

B. "orange banana apple"

C. "apple banana orange"

D. "apple banana apple"

Solution

1. Step 1: Understand SUBSTITUTE with instance number

   The formula replaces only the 2nd occurrence of "apple" with "orange".

2. Step 2: Apply replacement

   First "apple" stays, second "apple" changes to "orange" resulting in "apple banana orange".

3. Final Answer:

   "apple banana orange" -> Option C

4. Quick Check:

   SUBSTITUTE replaces 2nd 'apple' only [OK]

Hint: Use 4th SUBSTITUTE argument to replace specific occurrence [OK]

Common Mistakes:

• Replacing all occurrences instead of one
• Replacing first occurrence by mistake
• Confusing SUBSTITUTE with REPLACE

4. You want to replace the first 3 letters of the text in cell B2 with "XYZ". Which formula fixes the error in this incorrect formula: =SUBSTITUTE(B2, 1, 3, "XYZ")?

medium

A. =REPLACE(B2, "1", "3", "XYZ")

B. =REPLACE(B2, 1, 3, "XYZ")

C. =SUBSTITUTE(B2, "1", "XYZ", 3)

D. =SUBSTITUTE(B2, 3, 1, "XYZ")

Solution

1. Step 1: Identify misuse of SUBSTITUTE

   SUBSTITUTE does not use position or length, so it cannot replace by position.

2. Step 2: Use REPLACE for position-based replacement

   REPLACE takes start position, length, and new text, so =REPLACE(B2, 1, 3, "XYZ") is correct.

3. Final Answer:

   =REPLACE(B2, 1, 3, "XYZ") -> Option B

4. Quick Check:

   Use REPLACE for position-based text changes [OK]

Hint: Use REPLACE to change text by position, not SUBSTITUTE [OK]

Common Mistakes:

• Using SUBSTITUTE with numbers as positions
• Swapping parameters order
• Using quotes incorrectly around numbers

5. You have a list of product codes in column A like "AB12345". You want to replace the first two letters with "X5" and also replace all occurrences of "5" with "9" in the same formula. Which formula achieves this?

hard

A. =SUBSTITUTE(REPLACE(A1, 1, 2, "X5"), "5", "9")

B. =REPLACE(SUBSTITUTE(A1, "5", "9"), 1, 2, "X5")

C. =SUBSTITUTE(A1, LEFT(A1, 2), "X5")

D. =REPLACE(A1, 1, 2, "X5") & SUBSTITUTE(A1, "5", "9")

Solution

1. Step 1: Replace first two letters with REPLACE

   REPLACE(A1, 1, 2, "X5") changes first two letters to "X5".

2. Step 2: Replace all "5" with "9" using SUBSTITUTE

   SUBSTITUTE(..., "5", "9") changes all 5's to 9's in the updated text.

3. Step 3: Combine functions correctly

   Applying REPLACE first, then SUBSTITUTE on result is correct, matching =SUBSTITUTE(REPLACE(A1, 1, 2, "X5"), "5", "9").

4. Final Answer:

   =SUBSTITUTE(REPLACE(A1, 1, 2, "X5"), "5", "9") -> Option A

5. Quick Check:

   Chain REPLACE then SUBSTITUTE for combined changes [OK]

Hint: Nest REPLACE inside SUBSTITUTE to combine position and text changes [OK]

Common Mistakes:

• Applying SUBSTITUTE before REPLACE causing wrong results
• Concatenating results instead of nesting
• Using SUBSTITUTE to replace by position