When working on a large spreadsheet locating data becomes more difficult. Thus you need to use a function to speed things up. The search function is one of the functions used to locate and return values on Excel. In this article I will guide you on different ways that you can make use of the Excel Search function.
What does the Excel SEARCH function do?
The search function returns the position of a sub-string in a text. It is categorized as a string or text function in Excel.
Excel SEARCH function syntax
=SEARCH(find_text,within_text,[start_num])
Arguments
find_text: refers to the text that you wish to search
within_text: refers to the text string that contains the substring that you wish to find
Start_num: You can add or leave it. It’s the number of positions that you wish to search
Important points
- The search function is not case sensitive
- Uses of SEARCH function
- To find the position of a substring in a string
Example 1: How to find the first appearance of a text
How to find the first occurrence of a text in a string
Let us use the data in the screenshot below for our examples
=SEARCH(“rem”, “Lorem Ipsum”) returns 3
The function is case insensitive as seen with the example below
=SEARCH(“REM”, “Lorem Ipsum”) returns 3
Example 2: How to find a text by specifying the starting postion
You can also search the position of a substring by specifying the position that you wish to start.
=SEARCH(“rem”,”Lorem Ipsum Lorem”, 4) returns 15
Note that in this example, the function finds the position of the second rem since we have instructed it to start at position 4. Not that counting positions start at 1 but counting the substring starts at 4.
Example 3: How to use a wildcard in Excel SEARCH unction
A wildcard refers to a special character that one can use to represent a character in an Excel formula.
You can use a wildcard to find the position of a text in Excel.
=SEARCH(“cen”, “at the center of the shopping centre”) returns 8
Related Article: How to search inside Excel Files: 6 Simple Ways
EXample 4: How to use ISNUMBER and Excel SEARCH function
If you want to test for specific text, then you can combine ISNUMBER and SEARCH function. If the text is found then the formula will return:
In the data below we have applied this formula =ISNUMBER(SEARCH(C3,A2))
