FIND and SEARCH in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Find the position of a substring using FIND

What is the result of the formula =FIND("cat", "concatenate") in Google Sheets?

Google Sheets

=FIND("cat", "concatenate")

A 1

B 4

C 5

D Error

💡 Hint

Remember that FIND returns the position of the first character of the substring, counting from 1.

The substring "cat" starts at the 4th character in "concatenate" (c-o-n-ca-t-e-n-a-t-e). FIND is case-sensitive and returns 4.

📊 Formula Result

intermediate

SEARCH function with case insensitivity

What is the output of =SEARCH("Dog", "My dog is cute") in Google Sheets?

Google Sheets

=SEARCH("Dog", "My dog is cute")

A 4

B 1

C 7

D Error

💡 Hint

SEARCH is not case-sensitive, so it finds "Dog" even if the text has "dog" in lowercase.

SEARCH finds "Dog" starting at the 4th character in "My dog is cute" ignoring case.

❓ Function Choice

advanced

Choosing between FIND and SEARCH for case sensitivity

You want to find the position of "Apple" in the text "I like apple pie" but only if the case matches exactly. Which function should you use?

A SEARCH("apple", "I like apple pie")

B SEARCH("Apple", "I like apple pie")

C FIND("apple", "I like apple pie")

D FIND("Apple", "I like apple pie")

💡 Hint

FIND is case-sensitive, SEARCH is not.

FIND("Apple", "I like apple pie") returns an error because "Apple" with uppercase A is not found exactly. SEARCH would find it ignoring case. Since you want exact case match, FIND is correct.

🎯 Scenario

advanced

Extracting text after a keyword using FIND

You have the text "Order ID: 12345" in cell A1. Which formula extracts the number after "Order ID: " correctly?

A =MID(A1, FIND("Order ID: ", A1) + 10, LEN(A1))

B =MID(A1, FIND("Order ID: ", A1) + 9, LEN(A1))

C =RIGHT(A1, LEN(A1) - FIND("Order ID: ", A1))

D =MID(A1, SEARCH("Order ID: ", A1) + 9, LEN(A1))

💡 Hint

Count the length of the keyword "Order ID: " including the space to find the start position of the number.

The keyword "Order ID: " is 10 characters long. FIND returns the start position of the keyword, so adding 10 moves to the first character of the number. MID extracts from there to the end.

❓ data_analysis

expert

Counting occurrences of a substring using SEARCH

You want to count how many times the substring "cat" appears in cell A1 containing "catapult cat category scatter". Which approach is correct?

A =LEN(A1) - LEN(SUBSTITUTE(A1, "cat", ""))

B =COUNTIF(A1, "*cat*")

C =ARRAYFORMULA(SUM((LEN(A1) - LEN(SUBSTITUTE(LOWER(A1), "cat", ""))) / LEN("cat")))

D =SEARCH("cat", A1)

💡 Hint

Use SUBSTITUTE to remove the substring and compare lengths before and after to count occurrences.

Option C converts text to lowercase, removes all "cat" substrings, and calculates how many times "cat" appeared by length difference divided by substring length. Other options either count cells or find only first occurrence.