Excel: The Difference Between SEARCH vs. FIND Functions


Both the SEARCH function and FIND function in Excel can be used to find the location of one text string within another, but there are two differences between these functions:

1. The SEARCH function is not case-sensitive while the FIND function is case-sensitive.

2. The SEARCH function allows wildcard characters while the FIND function does not allow wildcard characters.

The following examples illustrate these two differences in practice with the following column of basketball team names in Excel:

Example 1: The Case-Sensitive Difference

Suppose we would like to use both SEARCH and FIND to identify the position of the first “s” in each team name.

We will type the following formulas into cells B2 and C2:

  • B2: =SEARCH(“s”, A2)
  • C2: =FIND(“s”, A2)

We will then click and drag these formulas down to each remaining cell in columns B and C:

Excel SEARCH vs. FIND functions case-sensitive

The SEARCH function is not case-sensitive, so it simply finds the position of the first “s” in each team name regardless of case. This is why it returns 1 for Spurs.

However, the FIND function is case-sensitive, so it finds the position of the first lowercase “s” in each team name. This is why it returns 5 for Spurs.

Example 2: The Wildcard Character Difference

Suppose we would like to use both SEARCH and FIND to identify the position of the first substring “rs” in each team name where any character can come before this particular substring.

We will type the following formulas into cells B2 and C2:

  • B2: =SEARCH(“?rs”, A2)
  • C2: =FIND(“?rs”, A2)

We will then click and drag these formulas down to each remaining cell in columns B and C:

Excel SEARCH vs. FIND functions with wildcards

The SEARCH function allows wildcard characters, so it is able to find the position of the first occurrence of “rs” in each team name in which any character is allowed to come before “rs” due to using the ? wildcard.

However, the FIND function does not allow wildcard characters. This is why it returns #VALUE! for each row in column B.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Use SEARCH Function to Search Multiple Values
Excel: How to Use FIND Function with Multiple Criteria
Excel: How to Find First Number in Text String

Featured Posts

Leave a Reply

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