In this post, we will learn about Excel SEARCH function. Excel SEARCH function seeks the location of a character in a string. And returns its position number in the string from the starting character.
Excel SEARCH function is very similar function to the Excel FIND function. The only difference between both of the function is, one is case-sensitive and another is not. And Excel FIND function does not work with wildcard characters where Excel SEARCH function does.
This function finds no difference between “D” and “d” but Excel FIND Function does. It works fine with wildcard characters; we can use “?” and” *” and can find ? or * by using “~?” or “~*”.
How to define Excel SEARCH function
“Excel SEARCH function seeks the position of a character or sub-string within a given string or text in a cell.”
Excel SEARCH Function
=SEARCH (find_text, within_text, [start_num})
find_text = the text character or sub-string we are searching for.
within_text= is the text string, within which we are looking for our text character.
[start_num] (Optional) if exist, it will start counting the position of the character after this number. If not given, then it will start counting the position number of the character from the beginning.
Things to remember
Excel SEARCH function will return #VALUE! Error when “find_text” is not appearing in the given string or text.
This function returns #VALUE! Error if “start_num” is “0” or a negative value.
Excel SEARCH function can easily work in case of “wildcard characters”. Not like FIND function (is case sensitive).
Excel SEARCH function will return #VALUE! Error when the value of “start_num” is greater than the total length of the given string or text.
This function is not case-sensitive; i.e. it will treat UPPER (F) and LOWER (f) characters as equal. Excel FIND function treats both characters separately.
Here is the function in short
=SEARCH (find this character, within this text string, [start counting after this number])
In the below image, we can see that character “a” in string “Excelnsg” is not appearing. So if this is the scenario then Excel SEARCH function returns #VALUE! error.
In this example, character “e” is appearing two times in the string “website.” Therefore, according to the rule, if start_num is not present then it will start counting from the first character in the string. In the same way, if start_num is present then it will start counting after a mentioned start_num. See example in image:-
Furthermore, we can see that if start_num is given as zero “0” in Excel SEARCH function. Then it will return a result as #VALUE! error.
The below-given example shows Excel SEARCH function is not a case-sensitive function. It deals with capital and small letter equally unlikely Excel FIND function. See in the below image “Very” and “very” both sub-string are getting the same result.
In this example, we can see that the value of start_num is greater than the length of given string. Therefore, in this case, it will return the result as #VALUE!. Same as in case of Excel FIND function.
This function is very useful for separating words from a string. If a string containing a space or any wildcard characters between the string. We can easily find the wildcard character and separate the string as shown in the example.