FIND and SEARCH in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to find the position of "cat" in cell A1 using FIND.

Excel

=FIND([1], A1)

Drag options to blanks, or click blank then click option'

A"dog"

B"cat"

Ccat

DA1

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put the text in quotes

Using a cell reference instead of text

Using SEARCH instead of FIND when case matters

The FIND function needs the text to find as a string in quotes. So "cat" is correct.

2fill in blank

medium

Complete the formula to find the position of "apple" in cell B2 using SEARCH (case-insensitive).

Excel

=SEARCH([1], B2)

Drag options to blanks, or click blank then click option'

Aapple

B"Apple"

CB2

D"apple"

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around the text

Using FIND instead of SEARCH for case-insensitive search

SEARCH needs the text to find as a string in quotes. It is case-insensitive, so "apple" works.

3fill in blank

hard

Fix the error in the formula to find "book" in cell C3 using FIND.

Excel

=FIND([1], C3)

Drag options to blanks, or click blank then click option'

A"book"

Bbook

CC3

D"Book"

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving out quotes around the text

Using a cell reference instead of text

The text to find must be in quotes. Without quotes, Excel gives an error.

4fill in blank

hard

Fill both blanks to find the position of "pen" in cell D4 starting from character 3 using SEARCH.

Excel

=SEARCH([1], D4, [2])

Drag options to blanks, or click blank then click option'

A"pen"

B1

C3

D"Pen"

Attempts:

3 left

💡 Hint

Common Mistakes

Not putting the text in quotes

Starting search from position 1 instead of 3

SEARCH("pen", D4, 3) starts searching from the 3rd character in D4 for "pen".

5fill in blank

hard

Fill all three blanks to create a formula that finds "tree" in cell E5 ignoring case, starting from character 2.

Excel

=SEARCH([1], [2], [3])

Drag options to blanks, or click blank then click option'

A"tree"

BE5

C2

D"Tree"

Attempts:

3 left

💡 Hint

Common Mistakes

Using FIND instead of SEARCH for case-insensitive search

Not putting text in quotes

Wrong starting position

SEARCH("tree", E5, 2) finds "tree" ignoring case, starting from character 2 in cell E5.

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