Introduction
The SEARCH function in Excel helps you find the position of a specific character, word, or text inside another text value. It is useful when working with employee names, email addresses, product codes, course names, and business records.
SEARCH is not case-sensitive, which means it treats uppercase and lowercase letters as the same. It returns the starting position of the text you are searching for.
SEARCH Function Syntax
=SEARCH(find_text, within_text, [start_num])
Arguments:
- find_text: The character or text you want to find.
- within_text: The text or cell where you want to search.
- start_num: Optional. The position from which Excel should begin searching. If omitted, Excel starts from position 1.
Important Points:
- SEARCH is not case-sensitive.
- It counts spaces and special characters as positions.
- It returns the position of the first matching occurrence from the specified starting position.
- If the search text is not found, Excel returns a
#VALUE!error. - SEARCH supports wildcard characters such as
*and?.
Question 1: Find a Character in an Employee Name
Problem Statement
An HR department wants to find the position of the letter a in employee names.
| Employee Name | Search Character | Position |
|---|---|---|
| Rahul | a | 2 |
| Amit | a | 2 |
| Priya | a | 5 |
| Karan | a | 2 |
Assume employee names are in A2 and the search character is in B2.
Excel Formula
=SEARCH(B2,A2)
Expected Result: For Rahul, the result is 2.
Explanation: The letter a appears at the second position in Rahul. SEARCH finds it without considering letter case.
Student Task: Enter Neha in A6 and A in B6. Find the position.
New Expected Result: 4
Question 2: Search for Text Without Considering Letter Case
Problem Statement
A training institute stores course names in different letter cases. It wants to find the position of the word excel in each course name.
| Course Name | Search Text | Position |
|---|---|---|
| Advanced EXCEL Course | excel | 10 |
| Basic Excel Training | EXCEL | 7 |
| Learn excel Online | Excel | 7 |
| COMPLETE EXCEL GUIDE | Excel | 10 |
Assume course names are in A2 and the search text is in B2.
Excel Formula
=SEARCH(B2,A2)
Expected Result: For Advanced EXCEL Course, the result is 10.
Explanation: SEARCH finds excel even though the course name contains EXCEL in uppercase.
Student Task: Enter Professional Excel Training in A6 and EXCEL in B6. Find the starting position.
New Expected Result: 15
Question 3: Find the Position of @ in Email Addresses
Problem Statement
A company wants to find the position of the @ symbol in employee email addresses.
| Email Address | Position of @ |
|---|---|
| amit@gmail.com | 5 |
| priya@yahoo.com | 6 |
| rahul@company.com | 6 |
| neha@vsit.in | 5 |
Assume email addresses are in A2.
Excel Formula
=SEARCH("@",A2)
Expected Result: For amit@gmail.com, the result is 5.
Explanation: SEARCH locates the @ symbol and returns its position in the email address.
Student Task: Enter rohit@company.in in A6 and find the position of @.
New Expected Result: 6
Question 4: Find a Word Inside Product Descriptions
Problem Statement
An inventory department wants to find the position of the word Laptop in product descriptions.
| Product Description | Search Text | Position |
|---|---|---|
| Dell Laptop 15 Inch | laptop | 6 |
| HP Laptop 14 Inch | LAPTOP | 4 |
| Lenovo Laptop Pro | Laptop | 8 |
| Acer Laptop Silver | LAPTOP | 6 |
Assume product descriptions are in A2 and the search text is in B2.
Excel Formula
=SEARCH(B2,A2)
Expected Result: For Dell Laptop 15 Inch, the result is 6.
Explanation: The word Laptop starts at the sixth character, including the space after Dell.
Student Task: Enter Samsung Laptop 16GB RAM in A6 and laptop in B6. Find the starting position.
New Expected Result: 10
Question 5: Search for Text Starting from a Specific Position
Problem Statement
A company uses repeated hyphens in product codes. The inventory department wants to find the second hyphen by starting the search from a specific position.
| Product Code | Search Character | Starting Position | Expected Position |
|---|---|---|---|
| LAP-IT-105 | – | 5 | 7 |
| KEY-HW-205 | – | 5 | 7 |
| MON-DP-310 | – | 5 | 7 |
| USB-AC-450 | – | 5 | 7 |
Assume product codes are in A2.
Excel Formula
=SEARCH("-",A2,5)
Expected Result: For LAP-IT-105, the result is 7.
Explanation: SEARCH starts looking from position 5 and finds the second hyphen at position 7.
Student Task: Enter MOUSE-WL-250 in A6. Find the second hyphen by starting the search from position 7.
New Expected Result: 8
Question 6: Use Wildcard * to Search for Text Patterns
Problem Statement
A sales department wants to find descriptions containing the word Phone followed by any number of characters.
| Product Description | Search Pattern | Position |
|---|---|---|
| Samsung Phone 128GB | Phone* | 9 |
| Apple Phone 256GB | Phone* | 7 |
| Redmi Phone 64GB | Phone* | 7 |
| Nokia Phone Basic | Phone* | 7 |
Assume product descriptions are in A2 and the search pattern is in B2.
Excel Formula
=SEARCH(B2,A2)
Expected Result: For Samsung Phone 128GB, the result is 9.
Explanation: The asterisk (*) is a wildcard that represents any number of characters, including no characters. SEARCH finds the position where the pattern begins.
Student Task: Enter OnePlus Phone 512GB in A6 and Phone* in B6. Find the starting position.
New Expected Result: 9
Question 7: Use Wildcard ? to Search for a Character Pattern
Problem Statement
A company stores product codes with different characters. It wants to search for codes containing IT followed by exactly one character.
| Product Code | Search Pattern | Position |
|---|---|---|
| EMP-IT5-101 | IT? | 5 |
| STAFF-IT2-205 | IT? | 7 |
| VSIT-IT8-305 | IT? | 6 |
| TEAM-IT3-405 | IT? | 6 |
Assume product codes are in A2 and the search pattern is in B2.
Excel Formula
=SEARCH(B2,A2)
Expected Result: For EMP-IT5-101, the result is 5.
Explanation: The question mark (?) represents exactly one character. SEARCH finds IT followed by one character.
Student Task: Enter SALES-IT7-501 in A6 and IT? in B6. Find the starting position.
New Expected Result: 7
Student Tasks
Solve the following exercises using only the SEARCH function.
Task 1: Find a Character in Text
| Text | Search Character | Position |
|---|---|---|
| COMPUTER | p | ? |
| KEYBOARD | b | ? |
| MONITOR | T | ? |
| PRINTER | e | ? |
Your Task: Find the position of each character. Remember that SEARCH is not case-sensitive.
Expected Results: 4, 5, 5, 5
Task 2: Search for Words in Course Names
| Course Name | Search Text | Position |
|---|---|---|
| Advanced Excel Course | EXCEL | ? |
| Basic Python Training | python | ? |
| Learn Power BI | POWER | ? |
| Complete SQL Course | sql | ? |
Your Task: Find the starting position of each word.
Expected Results: 10, 7, 7, 10
Task 3: Find the Second Hyphen
| Product Code | Search Character | Starting Position | Position |
|---|---|---|---|
| LAP-IT-105 | – | 5 | ? |
| KEY-HW-205 | – | 5 | ? |
| MON-DP-310 | – | 5 | ? |
| USB-AC-450 | – | 5 | ? |
Your Task: Find the second hyphen in each product code.
Expected Results: 7, 7, 7, 7
Final Student Challenge: Build a Product Code Search Checker
Problem Statement
An inventory department wants to search for the text IT inside product codes. The product codes may contain uppercase or lowercase letters. The department also wants to search for the @ symbol in supplier email addresses.
Given Data
| Product Name | Product Code | Supplier Email |
|---|---|---|
| Laptop | LAP-IT-105 | amit@company.com |
| Keyboard | KEY-it-205 | priya@company.com |
| Monitor | MON-IT-310 | rahul@company.com |
| Mouse | MOU-It-450 | neha@company.com |
| Printer | PRI-IT-505 | rohit@company.com |
Your Task
Add two columns:
- IT Position
- @ Position
Use the SEARCH function to locate IT in each product code and @ in each supplier email address.
Formula for IT Position:
=SEARCH("IT",B2)
Formula for @ Position:
=SEARCH("@",C2)
Copy both formulas down for all five products.
Expected Result
| Product Name | Product Code | IT Position | Supplier Email | @ Position |
|---|---|---|---|---|
| Laptop | LAP-IT-105 | 5 | amit@company.com | 5 |
| Keyboard | KEY-it-205 | 5 | priya@company.com | 6 |
| Monitor | MON-IT-310 | 5 | rahul@company.com | 6 |
| Mouse | MOU-It-450 | 5 | neha@company.com | 5 |
| Printer | PRI-IT-505 | 5 | rohit@company.com | 6 |
Key Takeaways
- SEARCH finds the position of a character or text inside another text value.
- Its syntax is
=SEARCH(find_text, within_text, [start_num]). - SEARCH is not case-sensitive.
- It counts spaces and special characters as positions.
- The optional start_num argument lets you begin searching from a specific position.
- The asterisk (
*) represents any number of characters. - The question mark (
?) represents exactly one character. - If the search text is not found, Excel returns a
#VALUE!error.
FAQs
1. What is the SEARCH function in Excel?
The SEARCH function finds the position of a specific character or text inside another text value.
2. What is the syntax of the SEARCH function?
The syntax is =SEARCH(find_text, within_text, [start_num]).
3. Is the SEARCH function case-sensitive?
No. SEARCH treats uppercase and lowercase letters as the same.
4. What is the difference between FIND and SEARCH?
FIND is case-sensitive, while SEARCH is not case-sensitive. SEARCH also supports wildcard characters such as * and ?.
5. What happens if SEARCH cannot find the text?
Excel returns a #VALUE! error when the specified text is not found.
6. What is the use of start_num in SEARCH?
The start_num argument tells Excel the position from which it should begin searching. It is useful when the same character appears more than once.
7. What does the asterisk (*) mean in SEARCH?
The asterisk represents any number of characters, including zero characters.
8. What does the question mark (?) mean in SEARCH?
The question mark represents exactly one character in a search pattern.
9. Can SEARCH locate symbols in email addresses?
Yes. SEARCH can locate symbols such as @, dots, hyphens, and other characters in email addresses.

