How to use Excel SEARCH function with example

Excel SEARCH Funcion

 

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})

 

Syntax

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])

Example 1

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.

Excel SEARCH Function

 

Example 2

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:-

Excel SEARCH function

 

Example 3

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.

Excel SEARCH Function

Example 4

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.

Excel SEARCH Function

 

Example 5

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.

Excel SEARCH Function

Example 6

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.

Excel SEARCH Function

 

Related Post

Excel LEFT Function, Excel RIGHT Function, Excel MID Function, Excel FIND function

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.