FIND VS SEARCH function in Microsoft Excel

 

The FIND function and SEARCH function are somewhat similar, but still there is a factor that distinguishes them to be selective for solving our query in Microsoft Excel.

FIND function in Microsoft Excel returns the position of a specified character or sub-string within a string or text.

Syntax:- =FIND(find_text,within_text,[start_num])

SEARCH function returns the position of the first character of sub-string or search_text in a string. Syntax:- =SEARCH(find_text,within_text,[start_num])

In the below table, we can see the difference between both functions:-

FIND SEARCH
This function is case-sensitive This function is not case-sensitive
It does not allow wildcard characters It allows the wildcard characters such as “?”, “*” etc.
This function search exact text This function does not check uppercase and lowercase letters

 

FIND Function

Let’s take examples and understand:-

Let’s go to the Excel sheet. We have data sets in cell A7 & A8. And, we will put the function in cell B7 & B8 to get the desired output.

image 1

 

In the above example, we want to find the place number of the character “Excellent”, follow below given steps:-

  • Enter the function in cell B7
  • =FIND(“Excellent”,A7,1)
  • Press Enter
  • The function will return 1

image 2

 

Formula Explanation: – First of all, find function will check the “Excellent” in the defined cell from the first position; from whatever position text will start, function will return as result. In the cell text, we can see that Excellent is starting from the first place of the cell text.

 

Let’s understand the case, in which function will not work

  • Now enter the function in cell B8 to find out the “Excel”
  • =FIND(“Excel”,A8,1)
  • Press Enter, the function will return #VALUE! Error

image 3

 

The function has given #VALUE! Error, because this function works is case-sensitive and, here in the function, we have mentioned “Excel”, and the same text is available in lower case in the cell.

 

SEARCH Function

image 4

 

First we want to find out the Excellent within a cell:-

  • Enter the function in cell B7
  • =SEARCH(“excellent”,A7,1)
  • Press Enter
  • The function will return 1

image 5

 

Note:- We haven’t defined the exact text for which we want to search the position.

Let’s understand the use of Wildcard characters in SEARCH function. Now, we will search for “Forum”

  • Enter the function in cell B8
  • =SEARCH(“?oru?”,A8,1)
  • Press Enter
  • The function will return 12

image 6

 

In this way, we can use wildcard character in SEARCH function to get the position number within cell text.

Note: – Another interesting point to mention about the FIND and the SEARCH function is that they have a 3rd optional argument. We can use this argument to indicate the position, counting from the left, from where we want to start searching.

 

image 48

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com



Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>