Table of Contents
The SEARCH and FIND functions are both used to locate specific text or characters within a larger string of data in Excel. However, there are subtle differences between them that are important to understand.
The SEARCH function looks for a specific text or character and returns the position of the first occurrence of that text within a cell. It is not case-sensitive, meaning it will find the text regardless of whether it is capitalized or not. Additionally, it allows for the use of wildcards to broaden the search criteria.
On the other hand, the FIND function is case-sensitive and only looks for an exact match of the specified text or character. It will return the position of the first occurrence of the exact text, and does not allow for the use of wildcards.
In summary, the main difference between the SEARCH and FIND functions in Excel is that the SEARCH function is more flexible and inclusive in its search criteria, while the FIND function is more specific and precise.
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:
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: