Find and replace in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Find and replace text in a cell

You have a cell A1 with the text "I love apples and apples are tasty". Which formula replaces all occurrences of "apples" with "oranges"?

A =REPLACE(A1, 1, 6, "oranges")

B =REPLACE(A1, FIND("apples", A1), 6, "oranges")

C =REGEXREPLACE(A1, "apples", "oranges")

D =SUBSTITUTE(A1, "apples", "oranges")

💡 Hint

Use a function that replaces all occurrences of a substring.

SUBSTITUTE replaces all occurrences of a substring by default. REPLACE replaces by position and length, so option D replaces only the first occurrence. REGEXREPLACE also works but is more complex. Option D replaces the first 6 characters regardless of content.

📊 Formula Result

intermediate

Replace only the second occurrence of a word

Cell A1 contains "red blue red blue red". Which formula replaces only the second "red" with "green"?

A =REGEXREPLACE(A1, "red", "green", 2)

B =SUBSTITUTE(A1, "red", "green")

C =SUBSTITUTE(A1, "red", "green", 2)

D =REPLACE(A1, FIND("red", A1, FIND("red", A1)+1), 3, "green")

💡 Hint

SUBSTITUTE has an optional fourth argument to specify which occurrence to replace.

Option C uses SUBSTITUTE with the occurrence number 2, so only the second 'red' is replaced. Option C replaces all occurrences. Option C is invalid because REGEXREPLACE does not take an occurrence argument. Option C replaces by position but requires a hardcoded length (3 for 'red').

❓ Function Choice

advanced

Choose the best function to replace text ignoring case

You want to replace all occurrences of "cat" with "dog" in cell A1, ignoring case (so "Cat", "CAT", "cat" all replaced). Which function should you use?

A =REGEXREPLACE(A1, "(?i)cat", "dog")

B =SUBSTITUTE(A1, "cat", "dog")

C =REPLACE(A1, FIND("cat", A1), 3, "dog")

D =LOWER(SUBSTITUTE(LOWER(A1), "cat", "dog"))

💡 Hint

Use a function that supports case-insensitive matching.

REGEXREPLACE with the (?i) flag makes the search case-insensitive, replacing all case variants. SUBSTITUTE is case-sensitive. Option A replaces only the first exact match and is case-sensitive. Option A converts all text to lowercase, replaces, but loses original case formatting.

❓ data_analysis

advanced

Count how many times a word appears before replacing

Cell A1 contains "apple banana apple orange apple banana". Which formula counts how many times "apple" appears?

A =(LEN(A1) - LEN(SUBSTITUTE(A1, "apple", ""))) / LEN("apple")

B =COUNTIF(A1, "apple")

C =ARRAYFORMULA(SUM(LEN(A1) - LEN(SUBSTITUTE(A1, "apple", ""))))

D =LEN(SPLIT(A1, " ")) - LEN(SUBSTITUTE(A1, "apple", ""))

💡 Hint

Count by comparing length before and after removing the word.

Option A calculates the difference in length before and after removing 'apple', then divides by length of 'apple' to get count. COUNTIF does not work on text inside a single cell. Option A sums a single number incorrectly. Option A mixes lengths of arrays and strings incorrectly.

🎯 Scenario

expert

Replace multiple different words in one formula

You want to replace "cat" with "dog" and "mouse" with "rat" in cell A1 in one formula. Which formula does this correctly?

A =REGEXREPLACE(A1, "cat|mouse", {"dog", "rat"})

B =SUBSTITUTE(SUBSTITUTE(A1, "cat", "dog"), "mouse", "rat")

C =ARRAYFORMULA(SUBSTITUTE(A1, {"cat", "mouse"}, {"dog", "rat"}))

D =REPLACE(REPLACE(A1, FIND("cat", A1), 3, "dog"), FIND("mouse", A1), 5, "rat")

💡 Hint

Try nesting SUBSTITUTE functions for multiple replacements.

Option B nests SUBSTITUTE calls to replace both words correctly. Option B tries to use an array in REGEXREPLACE which is invalid. Option B uses ARRAYFORMULA incorrectly with SUBSTITUTE. Option B replaces by position but only first occurrences and lengths are hardcoded, so it may fail if order changes.