How to Use Excel SEARCH Function with Examples
The SEARCH function in Excel is a valuable tool for finding a specific substring within a text string.
It allows you to locate the position of a specified character or text string within another text string.
In this tutorial, we will explore the syntax of the SEARCH function, its usage, and provide several tabular examples to demonstrate its practical applications.
1. Syntax of the SEARCH Function
The syntax of the SEARCH function is as follows:
=SEARCH(find_text, within_text, [start_num])
find_text: This is the text you want to find within the within_text.
within_text: This is the text string in which you want to search for find_text.
[start_num] (optional): This parameter specifies the starting position for the search within within_text. If omitted, the search starts from the beginning of the text string.
The SEARCH function returns the position of find_text within within_text as a number. If find_text is not found, it returns an error (#VALUE!).
2. Excel SEARCH Function - Examples
Let's dive into practical examples to understand how the SEARCH function works in Excel.
Basic Usage
Suppose you have a list of product names, and you want to find out if the word "apple" appears in each product name. You can use the SEARCH function as follows:
In this example, the SEARCH function is applied to each product name in column A, B, and C.
It will return the position of "apple" within the product name or an error if "apple" is not found.
Case-Insensitive Search
The SEARCH function is case-sensitive, meaning it will only find an exact match.
If you want to perform a case-insensitive search, you can use the LOWER function to convert both the search text and the target text to lowercase.
Here's an example:
In this example, we first convert the text in each cell to lowercase using the LOWER function and then search for the lowercase search term.
This allows us to perform a case-insensitive search.
Using SEARCH with Other Functions
You can combine the SEARCH function with other Excel functions to extract or manipulate data based on search results.
For instance, suppose you have a list of email addresses, and you want to extract the domain names.
You can use the SEARCH function along with the MID function:
In this example, the SEARCH function is used to find the position of the "@" symbol within each email address, and then the MID function is employed to extract the domain name.
Finding Multiple Instances
The SEARCH function can also be used to find the positions of multiple instances of a substring within a text string.
Let's say you have a paragraph of text, and you want to find the positions of all occurrences of the word "Excel":
In this example, the SEARCH function will find the positions of all occurrences of "Excel" in the text, and you can use it to perform various analyses, such as counting the occurrences or highlighting them.
Conclusion
The Excel SEARCH function is a versatile tool for finding specific substrings within text strings.
By understanding its syntax and usage, you can efficiently locate and manipulate data in your spreadsheets.
Whether you're performing basic searches, case-insensitive searches, or extracting information based on search results, the SEARCH function is a valuable addition to your Excel toolbox.
Use it to enhance your data analysis and text manipulation capabilities in Excel.
