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.