What is the purpose of the SEARCH function? How to use the SEARCH function in Excel with specific examples

For those who work with Excel files regularly, searching for information, characters, or extracting text to a predetermined column is definitely not a strange task. The SEARCH function is one of the Excel functions used to serve this need. So what is the SEARCH function and how to use it? Let’s find out in this article!

What is the SEARCH function?

The SEARCH function is one of the basic functions in Excel with the function of searching, locating, and processing data in the spreadsheet. The result returned by the SEARCH function is the number representing the position of the first occurrence of the text string to be searched in the source text string. The SEARCH function is also used to extract information from large data files, making it easy for users to analyze and summarize information. This function can also be nested with many other functions to serve the needs of Excel users.

Uses of the SEARCH function in Excel

• Support quick searching process

The SEARCH function performs the process of locating data quickly by just having the information string you want to search along with the source text string. In addition, the SEARCH function allows searching for information based on specific criteria, making it easy to determine the position of the data to be searched.

• Support data analysis process

Although the SEARCH function is used to search for data, you can also rely on the results of the function to determine the pattern of the data you are processing. At the same time, the querying function of the SEARCH function when combined with other functions also allows users to classify, sort, and analyze data more conveniently.

• Flexibility

The SEARCH function in Excel is also flexible when searching. Accordingly, users can customize criteria and conditions to determine the accurate information they are looking for.

• Ability to combine with other functions in Excel

By combining multiple Excel functions, users can upgrade formulas to serve their data processing needs. The SEARCH function can also be combined with many other important Excel functions such as RIGHT, MID, LEFT, LEN,…

How to use the SEARCH function in Excel

1. SEARCH function formula in Excel

=SEARCH (find_text, within_text, [start_num])

Where:

• find_text (required): The character string you want to search for.
• within_text (required): The source character string or text you want to search.
• start_num (optional): The search point in the source string.

Example: We search for the position of the letter M in cell C5 of the data table below and get the result as 3.

2. SEARCH function combined with LEFT function

We use the LEFT function along with the SEARCH function to extract the information before a specific character from left to right.

Example: To extract the character string before the first space in the source text string C3 in order from left to right, we use the formula:

=LEFT(C3, SEARCH(” “, C3)-1)

Where:

• LEFT: The Excel command performs extracting characters from left to right.
• C3: The cell to extract information from.
• -1: Eliminating 1 empty character.

=> Result: FPT.

3. SEARCH function combined with MID function

Example: To find the 6-character string in the middle of cell C5, we use the formula:

=MID(C5, SEARCH(” “, C5)+1, 6)

Where:

• MID: The command extracts characters from the middle of the text string.
• C5: The cell to extract information from.
• +1: Get the result after the distance.
• 6: Number of characters to extract.

=> Result: SEARCH.

4. SEARCH function combined with RIGHT function

The RIGHT function is the opposite of the LEFT function because it extracts data from right to left.

Example: To find the last character in cell C4, we use the formula:

=RIGHT(C4, SEARCH(” “, C4)+1)

Where:

• RIGHT: The command extracts characters from right to left.
• C4: The cell to extract information from.
• +1: The value added from the character “Find_text”.

=> Result: hoa.

5. SEARCH function combined with LEN function

The LEN function calculates the length (number of characters) of a text or character string. The result returned is an integer value representing the character index in the string, including spaces.

Example: To get the “Shop” character in cell C3, we use the formula:

=RIGHT(C3, LEN(C3)-SEARCH(” “, C3))

Where:

• RIGHT: The command extracts characters from right to left.
• C3: The cell to extract information from.
• LEN: The length of characters in the source text.

=> Result: Shop.

Conclusion

So, we have learned about the uses of the SEARCH function as well as how to use the SEARCH function with many other functions to determine and extract information more effectively. I hope this article can help you work with Excel data more quickly and easily. Currently, at FPT Shop, there are also many options of quality laptops suitable for office tasks. Visit to explore more!

Discover affordable laptop products here:

Affordable laptops

• What is the LEFT function? How to use the LEFT function in Excel, Google Sheet most effectively

• Introduction to the RIGHT function and how to use it in Excel