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.

Practice

1. What does the Find and replace tool in Google Sheets do?

easy

A. It deletes all cells containing the searched text.

B. It creates a new sheet with replaced data automatically.

C. It searches for specific text and replaces it with new text in the sheet.

D. It formats cells based on the searched text.

Solution

1. Step 1: Understand the purpose of Find and Replace

   The tool is designed to locate specific text or values in the sheet and replace them with new text or values.

2. Step 2: Compare options with the tool's function

   Only It searches for specific text and replaces it with new text in the sheet. correctly describes this function. Options B, C, and D describe actions not performed by Find and Replace.

3. Final Answer:

   It searches for specific text and replaces it with new text in the sheet. -> Option C

4. Quick Check:

   Find and Replace = Search and update text [OK]

Hint: Find and Replace changes text quickly without formulas [OK]

Common Mistakes:

• Thinking it creates new sheets automatically
• Confusing replace with delete
• Assuming it formats cells

2. Which of these is the correct way to open the Find and Replace tool in Google Sheets?

easy

A. Go to Edit menu and select 'Find and replace'.

B. Use the formula =FINDREPLACE() in a cell.

C. Right-click any cell and choose 'Find and replace'.

D. Press Ctrl + F and then click 'Replace' in the small search box.

Solution

1. Step 1: Recall how to access Find and Replace

   The Find and Replace tool is accessed from the Edit menu by selecting 'Find and replace'.

2. Step 2: Evaluate each option

   Press Ctrl + F and then click 'Replace' in the small search box. opens the simple Find box, not the full Find and Replace tool. Right-click any cell and choose 'Find and replace'. is incorrect because right-click does not show this option. Use the formula =FINDREPLACE() in a cell. is invalid because no such formula exists.

3. Final Answer:

   Go to Edit menu and select 'Find and replace'. -> Option A

4. Quick Check:

   Edit > Find and replace = Open tool [OK]

Hint: Use Edit menu for full Find and Replace tool [OK]

Common Mistakes:

• Using Ctrl+F only opens simple search
• Expecting right-click menu to have Find and Replace
• Trying to use a formula for Find and Replace

3. You have a sheet with the word 'apple' in cells A1, A2, and A3. You use Find and Replace to replace 'apple' with 'orange' and check 'Match case'. If cell A1 has 'Apple' (capital A), which cells will be changed?

medium

A. All A1, A2, and A3 will change to 'orange'.

B. Only A2 and A3 will change to 'orange'.

C. Only A1 will change to 'orange'.

D. No cells will change.

Solution

1. Step 1: Understand 'Match case' option effect

   When 'Match case' is checked, the search is case-sensitive. So 'apple' will not match 'Apple'.

2. Step 2: Identify which cells match exactly

   Cells A2 and A3 contain 'apple' exactly, so they will be replaced. A1 has 'Apple' with capital A, so it won't change.

3. Final Answer:

   Only A2 and A3 will change to 'orange'. -> Option B

4. Quick Check:

   Match case = case-sensitive replace [OK]

Hint: Match case means exact letter case must match [OK]

Common Mistakes:

• Ignoring case sensitivity and replacing all
• Assuming 'Apple' matches 'apple' with Match case on
• Replacing no cells by mistake

4. You tried to replace 'cat' with 'dog' but nothing changed. You had 'Cat' in your cells. What is the most likely reason?

medium

A. You had 'Match case' checked, so 'cat' did not match 'Cat'.

B. You forgot to click 'Replace all'.

C. You searched in formulas only, but 'Cat' was in values.

D. You used regular expressions incorrectly.

Solution

1. Step 1: Analyze the case difference

   The searched text was 'cat' lowercase, but cells had 'Cat' with uppercase C.

2. Step 2: Understand effect of 'Match case'

   If 'Match case' was checked, the search is case-sensitive, so 'cat' won't match 'Cat'. This explains why no replacements happened.

3. Final Answer:

   You had 'Match case' checked, so 'cat' did not match 'Cat'. -> Option A

4. Quick Check:

   Match case blocks case mismatches [OK]

Hint: Check 'Match case' if replacements don't happen [OK]

Common Mistakes:

• Assuming Replace all was not clicked
• Confusing formulas vs values search
• Misusing regular expressions without need

5. You want to replace all phone numbers in the format [phone] with 123-456-7890 using Find and Replace. Which option should you enable to do this efficiently?

hard

A. Enable 'Match case' and find '[phone]' exactly.

B. Use formulas to manually edit each phone number.

C. Use simple find '[phone]' and replace with '123-456-7890'.

D. Enable 'Search using regular expressions' and use \((\d{3})\) (\d{3})-(\d{4}) as find and $1-$2-$3 as replace.

Solution

1. Step 1: Understand the need for pattern matching

   Phone numbers vary, so to replace all in that format, regular expressions help match the pattern.

2. Step 2: Use regular expressions in Find and Replace

   Enabling 'Search using regular expressions' allows using patterns like \((\d{3})\) (\d{3})-(\d{4}) to find numbers and $1-$2-$3 to reformat them.

3. Final Answer:

   Enable 'Search using regular expressions' and use \((\d{3})\) (\d{3})-(\d{4}) as find and $1-$2-$3 as replace. -> Option D

4. Quick Check:

   Regex option = pattern replace [OK]

Hint: Use regex for pattern-based replacements [OK]

Common Mistakes:

• Not enabling regex for pattern matching
• Using exact text find for varying numbers
• Trying manual edits instead of Find and Replace