FIND and SEARCH in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a customer service analyst at an online retail company.

📋 Request: Your manager wants you to identify the position of specific keywords in customer feedback comments to analyze common issues.

📊 Data: You have a list of customer feedback comments in one column. You need to find where certain words like 'delay' and 'refund' appear in each comment.

🎯 Deliverable: Create a spreadsheet that shows the position of the words 'delay' and 'refund' in each comment using FIND and SEARCH functions.

Sample Data

Comment ID	Customer Feedback
1	My order had a delay but the refund was quick.
2	I want a refund because of the delay in delivery.
3	The product arrived on time, no issues.
4	Delay in shipping caused inconvenience.
5	Refund process was smooth and fast.
6	Customer service did not respond to my refund request.
7	There was a delay but no refund was needed.
8	Everything was perfect, no delay or refund issues.

1

Step 1: Insert a new column named 'Position of "delay" using FIND'. Use the FIND function to find the position of the word 'delay' in each feedback comment. The FIND function is case-sensitive.

=FIND("delay", B2)

Expected Result

For Comment ID 1, result is 15 (position where 'delay' starts). For Comment ID 3, returns an error because 'delay' is not found.

2

Step 2: Insert another column named 'Position of "refund" using SEARCH'. Use the SEARCH function to find the position of the word 'refund' in each feedback comment. SEARCH is not case-sensitive.

=SEARCH("refund", B2)

Expected Result

For Comment ID 1, result is 29. For Comment ID 3, returns an error because 'refund' is not found.

3

Step 3: Handle errors for comments where the word is not found by wrapping FIND and SEARCH with IFERROR to show 'Not found' instead of an error.

=IFERROR(FIND("delay", B2), "Not found") and =IFERROR(SEARCH("refund", B2), "Not found")

Expected Result

For Comment ID 3, both columns show 'Not found'. For Comment ID 1, positions as before.

4

Step 4: Copy the formulas down for all rows to analyze all comments.

Drag the formulas from row 2 down to row 9.

Expected Result

All comments show the position of 'delay' and 'refund' or 'Not found' if the word is missing.

Final Result

Comment ID | Customer Feedback                               | Position of "delay" using FIND | Position of "refund" using SEARCH
-------------------------------------------------------------------------------------------------------------
1          | My order had a delay but the refund was quick. | 15                            | 29
2          | I want a refund because of the delay in delivery.| 32                            | 10
3          | The product arrived on time, no issues.          | Not found                    | Not found
4          | Delay in shipping caused inconvenience.          | Not found                    | Not found
5          | Refund process was smooth and fast.               | Not found                    | 1
6          | Customer service did not respond to my refund request.| Not found                | 40
7          | There was a delay but no refund was needed.       | 13                            | 26
8          | Everything was perfect, no delay or refund issues.| 28                            | 37

✓The word 'delay' appears in most comments but is case-sensitive in FIND, so 'Delay' with capital D is not found by FIND.

✓SEARCH finds 'refund' regardless of case and returns the position correctly.

✓Using IFERROR helps to avoid errors and makes the report cleaner.

✓Positions help identify where keywords appear in the feedback for further analysis.

Bonus Challenge

Create a new column that extracts the word immediately following 'delay' or 'refund' in each comment to understand the context.

Show Hint

Use FIND or SEARCH to locate the keyword, then use MID and FIND to extract the next word after the keyword.

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