Find and replace in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Replace all occurrences of 'apple' with 'orange' in a column

You have a list of fruits in column A from A1 to A10. You want to replace every cell that contains the word 'apple' with 'orange'. Which method will do this correctly?

A Select column A, press Ctrl+H, type 'apple' in Find what and 'orange' in Replace with, then Replace All.

B Select column A, press Ctrl+H, type 'orange' in Find what and 'apple' in Replace with, then Replace All.

C Use Find (Ctrl+F), type 'apple', then Replace All with 'orange'.

D Select column A, press Ctrl+F, type 'orange', then Replace All with 'apple'.

💡 Hint

Remember, 'Find what' is the text you want to change, and 'Replace with' is the new text.

Option A correctly finds 'apple' and replaces it with 'orange'. Option A is incorrect because Find (Ctrl+F) does not have a Replace All function; you need to use Replace (Ctrl+H). Option A swaps the find and replace texts. Option A searches for 'orange' instead of 'apple'.

📊 Formula Result

intermediate

Result of SUBSTITUTE function replacing text

What is the result of this formula in cell B1 if A1 contains 'banana apple banana'?

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

A orange apple banana

B banana apple orange

C orange apple orange

D banana apple banana

💡 Hint

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

The formula replaces only the 2nd occurrence of 'banana' with 'orange'. The first 'banana' stays the same.

❓ Function Choice

advanced

Choose the function to replace text ignoring case

You want to replace all occurrences of 'cat' with 'dog' in a text string, but the text may have 'Cat', 'CAT', or 'cat'. Which function will replace all regardless of case?

A SUBSTITUTE(text, "cat", "dog")

B REPLACE(text, FIND("cat", text), 3, "dog")

C Use a combination of LOWER and SUBSTITUTE to replace 'cat' in lowercase text

D Use Find and Replace tool with Match case unchecked

💡 Hint

Excel's SUBSTITUTE is case-sensitive.

SUBSTITUTE is case-sensitive, so it won't replace 'Cat' or 'CAT'. REPLACE with FIND is also case-sensitive. Using LOWER changes text but loses original case. The Find and Replace tool with Match case off replaces all case variations.

❓ data_analysis

advanced

Count how many cells contain replaced text

You replaced 'old' with 'new' in a range B1:B20. Now you want to count how many cells contain the word 'new'. Which formula gives the correct count?

A =COUNTIF(B1:B20, "=new")

B =COUNTIF(B1:B20, "new")

C =COUNTIF(B1:B20, "*new*")

D =COUNT(B1:B20)

💡 Hint

Use wildcards to find text inside cells.

Option C counts cells containing 'new' anywhere. Option C counts only cells exactly equal to 'new'. Option C is invalid syntax. Option C counts numeric cells only.

📊 Formula Result

expert

Output of nested SUBSTITUTE replacing multiple words

Given cell A1 contains 'red blue green blue red', what is the result of this formula?

=SUBSTITUTE(SUBSTITUTE(A1, "blue", "yellow"), "red", "orange")

A orange yellow green yellow orange

B red yellow green yellow red

C orange blue green blue orange

D yellow orange green orange yellow

💡 Hint

SUBSTITUTE inside another SUBSTITUTE replaces text step by step.

The inner SUBSTITUTE replaces 'blue' with 'yellow'. Then the outer SUBSTITUTE replaces 'red' with 'orange'. So all 'blue' become 'yellow' and all 'red' become 'orange'.