SEARCH Function in Excel: Explained
In this article, you will learn about the SEARCH formula and its uses in Excel.
What is the SEARCH Function in Excel?
The SEARCH formula in Excel enables you to locate a specific character or group of characters within a text string. You can use the SEARCH function to quickly find and locate necessary data without manually searching through a large dataset. By returning the position number of the first character you searched for, the SEARCH formula helps you identify the exact location of the substring within the text.
Syntax of the SEARCH function in Excel
The syntax of the Excel SEARCH function is as follows:
- “find_text” is the substring or character you want to locate.
- “within_text” is the text string or cell reference within which you will look for your character(s).
- “start_num” (optional) is the position number of the character where you want your search to start. If the third argument is omitted, SEARCH starts looking from the first character of a text string.
How to use the SEARCH formula in Excel?
Understand the SEARCH function with the help of an example case. The below dataset is of various policyholders and their addresses. Now the objective is to figure out all those clients who live around “West Street”.
Mapping of the dataset with the syntax of the SEARCH function:
Find_text - “West Street” | Since we want to find the text “West Street”, You need to enclose the same within double quotation marks.
Within_text - Address Column or Cell C2 | Since the text “West Street” will be part of the client's address.
Start_num - omitted | In this sample case, we leave it blank because we want to search from the first character of the address string.
Hence our complete function would look like this:
Next, we need to press the Enter key to make the formula effective in cell D2.
Then, you can drag the cursor to cover all the cells where the function needs to be copied.
The function is instantly applied to all the cells, and you can see the results in Column D. The SEARCH function returns the respective position of the text string targeted in each text string. Wherever the function cannot find matches, it returns a #VALUE! Error. The function returns the matches irrespective of the text case.
Considering the results returned by the SEARCH function, we can identify the clients who live on West Street. Even for the same example case, if we define the “start_num” parameter to start searching from a particular character onwards, the results above would change. In the following example, we defined the “start_num” parameter as 20 in the above dataset. Hence, only one entry could indicate the presence of the text “West Street”, since the text “West Street” exists within the initial 20 characters for the other items in the list.
When should I use the SEARCH function in Excel?
The SEARCH function in Excel is most commonly used when locating a specific text string or value within a larger dataset. Here are a few scenarios where you might use the SEARCH function:
- Searching for a specific name, number, or keyword: If you have a large dataset with many different cells, use the SEARCH function to find all instances of a particular name, number, or keyword within that dataset. For example, as we showed above, you can check whether an address contains a specific street name (and, if so, where it is in a particular text string).
- Filtering and sorting data: The SEARCH function can also be used in conjunction with Excel's filtering and sorting tools to help you organize your data more effectively. For example, you can use the SEARCH function to identify all cells in a dataset that contain a particular text string and then filter or sort those cells based on another variable, such as date or value.
What do you need to know about the SEARCH function in Excel?
Here are some of the important notes about using the function:
- You should enclose each argument in double quotation marks to specify it as explicit text in Excel. However, when using cell references as arguments, you don’t need to use quotations.
- Unlike the FIND function, the SEARCH function is not case-sensitive by default. To perform a case-sensitive search, you should use the FIND function instead.
- If the SEARCH function cannot find a match for the specified text or value, it will return a #VALUE! Error.
- The SEARCH function in Excel allows you to use wildcards to search for specific text patterns within a larger string. This can be a helpful feature for searching through large datasets or complex formulas.
How to use Wildcards with the SEARCH function in Excel
In Excel, a wildcard is a special character that can be used as a placeholder for any other character or set of characters in a search or filter operation. Wildcards can be used with the SEARCH function to help you locate and manipulate data that meets specific criteria. The Wildcards in Excel are the asterisk (*), the question mark (?), and tilde (~).
Understand the SEARCH function using Wildcards with an example case. The dataset below consists of a list of financial institutions. The wildcards allow us to discover the occurrence of the text “Bank” in the provided dataset.
We will put the function as follows:
Where “*” represents any number of characters before or after the text. The output confirms whether each financial institution's name includes the word “Bank”. If it contains, the formula returns 1; if not, it gives you a “#VALUE!” error.
Next, find out the institute names with “Cent” by using the SEARCH function below.
This function will only identify text strings that contain the letters "Cent" followed by exactly two characters. It returns a #VALUE! Error for the text strings that do not meet this criterion.
If you observe carefully, Row 9 and 17 consist of the text “cent”; however, the formula doesn’t return 1 because they don’t have two characters following “cent”.
Lastly, try to find the institute names with a question mark (as a character, not a wildcard) in the below example dataset using the SEARCH function with a wildcard defined below. The tilde symbol makes a question mark or an asterisk next to it a normal character.
