What does the SEARCH function do?
Before learning how to use the SEARCH function in Excel let’s have a look at what it does. The SEARCH function in Excel is used to find the position of a specific character or substring within a text string. It returns the position as a number, which is useful for extracting or analyzing data. Unlike FIND, the SEARCH function is not case-sensitive and allows the use of wildcard characters (? and *).
Why is the SEARCH function useful?
There are several advantages to using the SEARCH function in Excel. Most notably, it enables flexible string searches by ignoring case sensitivity. Additionally, it supports wildcard characters such as ? (any single character) and * (any sequence of characters), making it more versatile. The function can also be combined with MID, LEFT, and RIGHT to extract specific data efficiently. Lastly, it facilitates error handling by returning a #VALUE! error when the search term is not found, which can be managed using IFERROR.
Syntax:
=SEARCH(find_text, within_text, [start_num])- find_text – The substring you want to locate.
- within_text – The text string where you want to search.
- start_num (optional) – The position to start the search from (default is 1).
How to use the SEARCH function in Excel
Examples of SEARCH function usage
Example 1: Basic Search
Find the position of “day” in “Monday”:
=SEARCH("day", "Monday")Result: 4 (as “day” starts at the 4th character)
Example 2: Search Ignoring Case
Find “apple” in “Green Apple”:
=SEARCH("apple", "Green Apple")Result: 7 (Excel ignores case sensitivity)
Example 3: Using Wildcards
Find the position of “a” followed by any two characters in “banana”:
=SEARCH("a??", "banana")Result: 2 (the first match is “ana” starting at position 2)
Example 4: Extracting Text Before a Character
Get the first name from “John_Doe” (separated by _):
=LEFT(A1, SEARCH("_", A1) - 1)Result: If A1 contains "John_Doe", the result will be “John”.
Example 5: Handling Errors When Text is Not Found
Prevent errors when searching for “xyz” in “abcdef”:
=IFERROR(SEARCH("xyz", "abcdef"), "Not Found")Result: "Not Found" (instead of #VALUE!)
The SEARCH function is a powerful tool for locating text within a string. It is especially useful for data cleaning, text extraction, and analysis, particularly when combined with other text functions. 🚀
See also our products and tools
Gumroad Shop: