FIND and SEARCH in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the FIND function do in Excel?

FIND locates the position of one text string inside another. It tells you where a specific word or letter starts in a cell. It is case-sensitive.

beginner

How is SEARCH different from FIND in Excel?

SEARCH also finds the position of text inside another text but is not case-sensitive. It can find text regardless of uppercase or lowercase letters.

beginner

What happens if FIND or SEARCH does not find the text?

Both FIND and SEARCH return an error (#VALUE!) if the text you are looking for is not found in the cell.

beginner

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

The formula is =FIND("cat", A1). It returns the starting position number of "cat" in the text inside A1, considering case.

intermediate

Can FIND and SEARCH start looking from a position other than the first character? How?

Yes, both functions have an optional third argument to specify the starting position. For example, =FIND("a", A1, 3) starts looking from the 3rd character.

Which function is case-sensitive when searching text in Excel?

A NEITHER FIND nor SEARCH

B FIND

C BOTH FIND and SEARCH

D SEARCH

FIND is case-sensitive, meaning it distinguishes uppercase and lowercase letters. SEARCH is not case-sensitive.

What result do FIND and SEARCH return if the text is not found?

A 0

B Empty string

C FALSE

D #VALUE! error

Both FIND and SEARCH return a #VALUE! error if the text to find is not present.

Which formula finds the position of "dog" in cell B2 ignoring case?

A =FIND("dog", B2)

B =FIND("DOG", B2)

C =SEARCH("dog", B2)

D =SEARCH("DOG", B2, 2)

SEARCH ignores case, so it finds "dog" regardless of letter case.

How do you start searching from the 5th character in a text using FIND?

A =FIND(text, cell, 5)

B =FIND(text, cell)

C =FIND(text, 5, cell)

D =FIND(5, text, cell)

The third argument in FIND is the starting position number.

If cell A1 contains "Hello World", what does =SEARCH("world", A1) return?

A 7

B #VALUE!

C 6

D 11

SEARCH is not case-sensitive and finds "world" starting at position 7.

Explain the difference between FIND and SEARCH functions in Excel.

Think about how they treat uppercase and lowercase letters.

Describe a real-life example where you would use FIND or SEARCH in a spreadsheet.

Imagine you have a list of email addresses or product codes.

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