SEARCH Function in Excel Tutorial

Excel Tutorial

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 NameSearch CharacterPosition
Rahula2
Amita2
Priyaa5
Karana2

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 NameSearch TextPosition
Advanced EXCEL Courseexcel10
Basic Excel TrainingEXCEL7
Learn excel OnlineExcel7
COMPLETE EXCEL GUIDEExcel10

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 AddressPosition of @
amit@gmail.com5
priya@yahoo.com6
rahul@company.com6
neha@vsit.in5

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 DescriptionSearch TextPosition
Dell Laptop 15 Inchlaptop6
HP Laptop 14 InchLAPTOP4
Lenovo Laptop ProLaptop8
Acer Laptop SilverLAPTOP6

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 CodeSearch CharacterStarting PositionExpected Position
LAP-IT-105–57
KEY-HW-205–57
MON-DP-310–57
USB-AC-450–57

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 DescriptionSearch PatternPosition
Samsung Phone 128GBPhone*9
Apple Phone 256GBPhone*7
Redmi Phone 64GBPhone*7
Nokia Phone BasicPhone*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 CodeSearch PatternPosition
EMP-IT5-101IT?5
STAFF-IT2-205IT?7
VSIT-IT8-305IT?6
TEAM-IT3-405IT?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

TextSearch CharacterPosition
COMPUTERp?
KEYBOARDb?
MONITORT?
PRINTERe?

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 NameSearch TextPosition
Advanced Excel CourseEXCEL?
Basic Python Trainingpython?
Learn Power BIPOWER?
Complete SQL Coursesql?

Your Task: Find the starting position of each word.

Expected Results: 10, 7, 7, 10

Task 3: Find the Second Hyphen

Product CodeSearch CharacterStarting PositionPosition
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 NameProduct CodeSupplier Email
LaptopLAP-IT-105amit@company.com
KeyboardKEY-it-205priya@company.com
MonitorMON-IT-310rahul@company.com
MouseMOU-It-450neha@company.com
PrinterPRI-IT-505rohit@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 NameProduct CodeIT PositionSupplier Email@ Position
LaptopLAP-IT-1055amit@company.com5
KeyboardKEY-it-2055priya@company.com6
MonitorMON-IT-3105rahul@company.com6
MouseMOU-It-4505neha@company.com5
PrinterPRI-IT-5055rohit@company.com6

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.