Table of Contents
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:
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)
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.