What are Excel FIND and SEARCH functions?

Suppose we have a sentence “Excel has plenty of functions.” We want to find the position of “plenty” in that sentence. The position means from which number the plenty is starting from in that sentence.

This is what Excel SEARCH and FIND functions are built for. Both functions take three arguments; two should already be clear. See the syntax of FIND and SEARCH that is almost the same:

FIND(Text_to_search, Source_Text, [start_num])

SEARCH(find_text,within_text,[start_num])

• Text to search – “Plenty” in our example sentence.
• Source_Text – The sentence we used. Naturally, this can be a cell reference.
• The third optional parameter is start number. It specifies what position you want to start searching in the Source text. For example, we want to search “c” in above sentence from 6 number. I will show this in the example to make things clear.
• If the find_text is not found, a #VALUE! Error occurs that you may be managed by using IFERROR.

The Difference between SEARCH and FIND in Excel

There are two differences:

• The FIND is case sensitive. The ‘C’ and ‘c’ has different meanings if you use FIND function to get the position of text in the source string.
• On the other hand, the SEARCH function is case-insensitive.
• You may not use the ‘*’ and ‘?’ wildcards in the FIND function.
• The SEARCH function allows using wildcards.

See the following section for the examples to learn both function and understanding the difference.

An example of FIND function

Let me start with a simple example of using a sentence and finding a word’s position in that sentence. The FIND formula is applied in the D11 cell as follows:

1	=FIND("Name","My Name is Ben")

The result:

The word “Name” is starting at position 4 in the given sentence.

Excel FIND function is case-sensitive example

Now, let us see the example of FIND function as case-sensitive. Using the same sentence as above except we will find the location of “name”:

1	=FIND("name","My Name is Ben")

You see, a #VALUE! error occurred as we changed the case of ‘N’ to ‘n’ in the FIND function.

Using the FIND function in a cell text

In this example, I will use the FIND function to search the position of a letter in A7 cell. The A column contains the names of employees for the demo only:

The FIND formula:

1	=FIND("h",A7)

The resultant sheet:

You can see the location of ‘h’ is 2 in the A1 text = Shabee

The example of Start_Num argument in FIND function

For the start_num parameter example, the text in B3 cell is searched. The B3 contains the following text:

“IT Expert / Excel / Powerpoint / Word”

We searched ‘p’ that occurs at position 6 and 26. However, the start_num argument is specified as below and see the result:

=FIND(B10,B3,8)

The result:

You see, it returned 26 because FIND started searching from position 8.

Handling the error if text not found by IFERROR function

Rather than showing an error message if the given search term is not found, you may use the IFERROR function with FIND for displaying a descriptive message e.g. “Search term not found”.

See an example below where a user may enter any text in the B10 cell. The entered text will be searched in B3 cell. I used the IFERROR/FIND combination and entered various texts including that does not exist. Have a look at formula and results:

The Formula:

1	=IFERROR(FIND(B10,B3), "Given text is not found!")

The results:

See another one with IFERROR message:

The Visio is not found in the B3 cell. Rather than showing an error, the “Given text is not found!” message is displayed.

Excel SEARCH function

All the above examples will work the same way if you just replace the word “FIND” by “SEARCH” except the case sensitive example. So, I will not go further with showing all these examples for SEARCH function again but proceeding by showing how it is different to FIND function.

So let us first see the case-insensitive feature of the SEARCH function.

For that, I am using the same sheet as in above example. In the SEARCH formula, I specified the B8 cell that contains “Accounts” text. Have a look at results as I search various texts by using this formula:

The SEARCH formula:

=SEARCH(B10,B8)

The results:

You see, “aCC” is searched and it returned position is 1.

‘T’ is not used in “Accounts” yet SEARCH returned ‘t’ position.

Now searching the ‘t’:

Same result!

Using wildcards in SEARCH function

Taking further the same example, let us now check SEARCH with “*” and “?” wildcards. I just changed the searching cell from B8 back to B3:

=SEARCH(B10,B3)

With * wildcard:

With ‘?’ wildcard:

If I had used five ‘?’ leading question marks, the result would have been 1. Because one ‘?’ replaces one character. Due to six, it picked the second occurrence of ‘p’ i.e. “Power”.