11 Examples to Learn Excel Find and Search Functions (Minor Difference)

In summary, both FIND and SEARCH are useful for text searching in Excel, but they differ in case sensitivity and wildcard support. Use FIND when case-sensitivity matters, and SEARCH for case-insensitive searches with wildcard capabilities.

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

In this tutorial: hide

1. The Difference between SEARCH and FIND in Excel
2. An example of FIND function
3. Excel FIND function is a case-sensitive example
4. Using the FIND function in a cell text
5. The example of Start_Num argument in FIND function
6. Handling the error if text is not found by the IFERROR function
7. Excel SEARCH function
8. Using wildcards in the SEARCH function

The Difference between SEARCH and FIND in Excel

There are two differences:

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

See the following section for examples to learn both functions and understand 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:

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

The result:

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

Did you know, the Excel has FINDB and SEARCHB functions that works with languages that use double-byte characters set (DBCS). FIND/SEARCH is for single-byte characters set (SBCS) - count each character as 1.

Excel FIND function is a case-sensitive example

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

=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 for the position of a letter in the A7 cell. The A column contains the names of employees for the demo only:

The FIND formula:

=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 positions 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 is not found by the 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 the B3 cell. I used the IFERROR/FIND combination and entered various texts including those that do not exist. Have a look at the formula and results:

The Formula:

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

The results:

See another one with an 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.

Note: If you copy/paste this formula then carefully check the double quotes or a name error may occur.

Excel SEARCH function

All the above examples will work the same way if you just replace the word “FIND” with “SEARCH” except the case sensitive example. So, I will not go further with showing all these examples for SEARCH function again but proceed by showing how it is different from 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 the “Accounts” text. Have a look at the results as I search various texts by using this formula:

The SEARCH formula:

=SEARCH(B10,B8)

The results:

You see, "aCC" is searched and its returned position is 1.

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

Now searching the ‘t’:

Same result!

Using wildcards in the 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”.