When to use Excel SEARCH Function
Excel SEARCH function can be used when you want to locate a text string within another text string and find its position.
What it Returns
It returns a number that represents the starting position of the string you are finding in another string.
Syntax
=SEARCH(find_text, within_text, [start_num])
Input Arguments
find_text – the text or string that you need to find. within_text – the text within which you want to find the find_text argument. [start_num] – a number that represents the position from which you want the search to begin. If you omit it, it starts from the beginning.
Additional Notes
If the start number is not specified, then it starts looking from the beginning of the string. SEARCH function is not case-sensitive. If you want to do a case-sensitive search, use FIND function. SEARCH function can handle wildcard characters. There are three wildcard characters in Excel – the question mark (?), asterisk (*), and tilde (~). A question mark matches any single character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. It returns a #VALUE! error if the searched string is not found in the text.
Excel SEARCH Function – Examples
Here are four examples of using Excel SEARCH function:
#1 Searching for a Word in a Text String (from the beginning)
In the above example, when you search for the word good in the text Good Morning, it returns 1, which is the position of the starting point of the searched word. Note that Excel SEARCH function is not case sensitive. So you get the same result whether you use good, Good, or GOOD. If you are looking for a case sensitive search, use Excel FIND function.
#2 Searching for a Word in a Text String (with a specified beginning)
The third argument in the SEARCH function is the position within the text from where you want to start the search. In the example above, the function returns 1 when you search for the text good in Good Morning and the starting position is 1. However, it returns an error when you make it start at 2. Hence, it looks for the text good in ood Morning. Since it can not find it, it returns an error. Note: If you skip the last argument and don’t provide the starting position, by default it takes it as 1.
#3 When there are Multiple Occurrence of the Searched Text
Excel SEARCH function starts looking in the specified text from the specified position. In the above example, when you look for the text good in Good Good Morning with the starting position as 1, it returns 1, as it finds it at the beginning. When you start the search from the second character onwards, it returns 6, as it finds the matching text at the sixth position.
#4 Using Wildcard Characters in Excel Search Function
Excel SEARCH Function can also handle wildcard characters. In the above example, the searched text is c*l, which means any text string that beings with c and end with l and can have any number of characters in between. In Excel, it finds the searched string at the third position (Excel) and returns 3.
Excel SEARCH Function – Video Tutorial
Related Excel Functions:
Excel FIND Function. Excel LOWER Function. Excel UPPER Function. Excel PROPER Function. Excel REPLACE Function. Excel SUBSTITUTE Function.
Search and Highlight Data Using Conditional Formatting. Create Dynamic Filter in Excel. Create Drop Down List with Search Suggestions. Separate First and Last Name in Excel
