Excel SEARCH Function
Search function in excel is categorized under text or string functions but the output returned by this function is integer, search function gives us the position of a substring in a given string when we give a parameter of the position to search from, thus this formula takes three arguments one is the substring, one is the string itself and one is the position to start the search.
The SEARCH function is text function which is used to find the location of a substring in a string/text.
The SEARCH function can be used as a worksheet function and it is not case sensitive function.
SEARCH Formula in Excel
Below is the SEARCH Formula in Excel
Excel SEARCH function has three-parameter two (find_text, within_text) are compulsory parameters and one (start_num) is optional.
- find_text: find_text refers to the substring/character which you want to search within a string or the text you want to find out.
- within_text:. Where your substring is located or where you perform the find_text.
- [start_num]:: from where you want to start the SEARCH within the text in excel. if omitted then SEARCH considers it as 1 and star search from the first character.
How to Use the Search Function in excel? (with Examples)
SEARCH function is very simple and easy to use. Let’s understand the working of SEARCH Function by some examples.
Let’s perform a simple search of “Good” substring in the given text or string. Here we have searched the Good word using SEARCH function and it will return the location of Good word in the Good morning.
=SEARCH(“Good”, B6) and output will be 1.
And suppose there are two matches found for good then SEARCH in excel will give you the first match value if you want the other good location then you just use the =SEARCH(“Good”, B7, 2) [start_num] as 2 then it will give you the location of second match value and output will be 6.
In this example, we will filter out the first name and last name from the full name using the SEARCH in excel.
For first name=LEFT(B12,SEARCH(” “,B12)-1)
For Last name=RIGHT(B12,LEN(B12)-SEARCH(” “,B12))
Suppose there is a set of IDs and you have to find out the _ location within IDs then use simply the Excel SEARCH to find out the “_” location within IDs.
=SEARCH(“_”, B27) and output will be 6.
Let’s understand the working of SEARCH in excel with wildcards characters.
Consider the given table and search for the next 0 within the text A1-001-ID
And start position will be 1 then =SEARCH(“?”&I8, J8, K8) output will be 3 because “?” neglect the one character before the 0 and output will be 3.
For the second row within a given table the search result for A within B1-001-AY
Will be 8 but if we use “*” in search it will give you the 1 as location output because it will neglect all character before “A” and output will be 1 for it =SEARCH(“*”&I9, J9).
Similarly for “J” 8 for=SEARCH(I10,J10,K10) and 7 for =SEARCH(“?”&I10,J10,K10).
Similarly for fourth row the output will be 8 for =SEARCH(I11,J11,K11) and 1 for =SEARCH(“*”&I11,J11,K11)
Things to Remember
- It is not case-sensitive
- It considers Tanuj and Tanuj as the same value means it does not distinguish b/w lower case and upper case.
- It is also allowed wildcard characters i.e. “?” ,“*” and “~” tilde.
- “?” is used to find a single character.
- “*” is used for match sequence.
- If you actually want to search the “*” or “?” then use the “~” before the character.
- It returns the #VALUE! Error if there is no matching string is found in the within_text.
Suppose in the below example we are searching for a substring “a” within the “Name” column if found it will return the location of a within name else it will give #VALUE error as shown below.
Search Function in Excel Video
This has been a guide to SEARCH Function in Excel. Here we discuss the SEARCH Formula and how to use SEARCH function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –