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.

Practice

1. What is the main difference between the FIND and SEARCH functions in Excel?

easy

A. FIND returns the length of the text; SEARCH returns the position.

B. SEARCH only works with numbers; FIND works with text.

C. FIND is case-sensitive; SEARCH is not case-sensitive.

D. SEARCH is case-sensitive; FIND is not case-sensitive.

Solution

1. Step 1: Understand the behavior of FIND

   The FIND function looks for a substring inside text and is case-sensitive, meaning it treats uppercase and lowercase letters differently.

2. Step 2: Understand the behavior of SEARCH

   The SEARCH function also finds a substring but ignores case differences, so it is not case-sensitive.

3. Final Answer:

   FIND is case-sensitive; SEARCH is not case-sensitive. -> Option C

4. Quick Check:

   Case sensitivity difference = FIND is case-sensitive; SEARCH is not case-sensitive. [OK]

Hint: Remember: FIND cares about case, SEARCH ignores it [OK]

Common Mistakes:

• Thinking SEARCH only works with numbers
• Confusing which function is case-sensitive
• Believing FIND returns length instead of position

2. Which of the following is the correct syntax for the FIND function in Excel?

easy

A. =FIND(find_text, within_text, [start_num])

B. =FIND(within_text, find_text, [start_num])

C. =FIND(find_text, start_num, within_text)

D. =FIND(start_num, find_text, within_text)

Solution

1. Step 1: Recall the order of arguments in FIND

   The correct order is find_text (the text to find), within_text (where to look), and optional start_num (where to start).

2. Step 2: Match the syntax with options

   =FIND(find_text, within_text, [start_num]) matches this order exactly, so it is correct.

3. Final Answer:

   =FIND(find_text, within_text, [start_num]) -> Option A

4. Quick Check:

   Correct argument order = =FIND(find_text, within_text, [start_num]) [OK]

Hint: Remember: FIND(find_text, within_text, start_num) [OK]

Common Mistakes:

• Swapping find_text and within_text arguments
• Placing start_num before within_text
• Using incorrect argument order causing errors

3. Given cell A1 contains the text "Hello World", what will the formula =FIND("o", A1) return?

medium

A. 8

B. 5

C. 7

D. 6

Solution

1. Step 1: Identify the position of "o" in "Hello World"

   The text is "Hello World". Counting letters: H(1) e(2) l(3) l(4) o(5) space(6) W(7) o(8) r(9) l(10) d(11). The first "o" appears at position 5.

2. Step 2: Understand FIND returns first occurrence position

   FIND("o", A1) returns the position of the first "o", which is 5.

3. Final Answer:

   5 -> Option B

4. Quick Check:

   First "o" position = 5 [OK]

Hint: Count characters to find first match position [OK]

Common Mistakes:

• Choosing position of second 'o' instead of first
• Counting space incorrectly
• Confusing FIND with SEARCH case sensitivity

4. You have the formula =FIND("apple", B1) but it returns an error. Which fix will most likely solve the problem?

medium

A. Replace FIND with SEARCH to ignore case

B. Add a start number argument like =FIND("apple", B1, 5)

C. Use =FIND(B1, "apple") instead

D. Change "apple" to "Apple" if B1 contains "Cherry pie"

Solution

1. Step 1: Understand FIND is case-sensitive

   If B1 contains "Apple pie" with capital A, =FIND("apple", B1) will error because "apple" (lowercase) is not found exactly.

2. Step 2: Use SEARCH to ignore case

   Replacing FIND with SEARCH ignores case differences, so =SEARCH("apple", B1) will find "Apple" regardless of case.

3. Final Answer:

   Replace FIND with SEARCH to ignore case -> Option A

4. Quick Check:

   Use SEARCH for case-insensitive find [OK]

Hint: Use SEARCH to avoid case errors [OK]

Common Mistakes:

• Swapping arguments incorrectly
• Adding start_num without fixing case
• Changing text case without changing function

5. You want to find the position of the second occurrence of the letter "e" in cell A2 containing "Experience". Which formula will correctly return this position?

hard

A. =SEARCH("e", A2, 2)

B. =SEARCH("e", A2) + 1

C. =FIND("e", A2, 2)

D. =FIND("e", A2, FIND("e", A2) + 1)

Solution

1. Step 1: Find first occurrence position of "e"

   Using FIND("e", A2) returns the position of the first "e" in "Experience".

2. Step 2: Use that position + 1 as start_num to find second occurrence

   To find the second "e", start searching just after the first found position: FIND("e", A2, FIND("e", A2) + 1).

3. Final Answer:

   =FIND("e", A2, FIND("e", A2) + 1) -> Option D

4. Quick Check:

   Nested FIND finds second occurrence [OK]

Hint: Nest FIND to locate second occurrence [OK]

Common Mistakes:

• Using fixed start_num without first FIND
• Adding 1 to SEARCH result incorrectly
• Using SEARCH with wrong start_num