FIND and SEARCH in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Find the position of a substring with FIND

Given the text "Hello World" in cell A1, what is the result of the formula =FIND("o", A1)?

Excel

=FIND("o", A1)

A 7

B 5

C 4

D 8

💡 Hint

Remember FIND counts characters starting at 1 and is case-sensitive.

The first lowercase "o" in "Hello World" is the 5th character.

📊 Formula Result

intermediate

SEARCH function ignoring case

If cell A1 contains "Excel Tips", what does =SEARCH("t", A1) return?

Excel

=SEARCH("t", A1)

A #VALUE!

B 6

C 8

D 7

💡 Hint

SEARCH is not case-sensitive, so it finds both uppercase and lowercase matches.

The letter "t" (case-insensitive) first appears at position 7 in "Excel Tips".

❓ Function Choice

advanced

Choosing between FIND and SEARCH for case sensitivity

You want to find the position of "Data" in cell A1, but only if it matches the exact case. Which function should you use?

A SEARCH("data", A1)

B SEARCH("Data", A1)

C FIND("Data", A1)

D FIND("data", A1)

💡 Hint

One function is case-sensitive, the other is not.

FIND is case-sensitive, so it only matches exact case.

🎯 Scenario

advanced

Handling errors when substring is not found

You use =FIND("x", "apple"). What will happen and how can you avoid an error?

Excel

=FIND("x", "apple")

A Returns #VALUE! error; use IFERROR to handle it

B Returns empty string; no error

C Returns FALSE; use ISERROR to check

D Returns 0; no error

💡 Hint

FIND returns an error if substring is missing.

FIND returns a #VALUE! error if the substring is not found. Wrapping it with IFERROR can prevent errors.

❓ data_analysis

expert

Count how many times a letter appears using FIND or SEARCH

You want to count how many times the letter "a" appears in cell A1 containing "Banana". Which formula correctly counts all occurrences?

A =LEN(A1)-LEN(SUBSTITUTE(LOWER(A1), "a", ""))

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

C =FIND("a", A1)

D =SUMPRODUCT(--(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)="a"))

💡 Hint

Think about removing letters and comparing lengths.

Option A counts all 'a' letters by comparing length before and after removing 'a'.

Option A only checks if 'a' exists, not count.

Option A is case-sensitive and misses uppercase 'A'.

Option A finds only first occurrence.