Excel’s SEARCH and FIND functions work in very similar ways, returning the numeric position of a substring in a target string, but with the important difference that FIND is case sensitive, while SEARCH is case insensitive.
If the text string is not found, the function returns a #VALUE error.
N.B. The SEARCHB & FINDB functions are similar to their SEARCH & FIND function equivalent, but counts 2 bytes per character. This is only relevant for when double-byte character set (DBCS) language character sets is set as the default language (such as Japanese, Chinese and Korean). Otherwise SEARCHB & FINDB behaves the same as SEARCH & FIND respectively, counting 1 byte per character.
N.B. The SEARCH and SEARCHB functions are not case sensitive. If you want to do a case sensitive search, use FIND and FINDB.
Function Syntax
Both SEARCH and FIND use equivalent syntaxes
=SEARCH(find_text,target_text,[start_num])
=FIND(find_text,target_text,[start_num])
The function takes text substring find_text value and returns the position of the first result found in the target_text, after the (optional) starting character position specified in the start_num in the target text. start_num must be 1 or greater. If the optional start_num is not provided, the function will start at the first character of the target_text.
Usage
Used to return the position of the start of a substring, identified within a longer target string. The function is often used with other text functions, such as MID or LEFT and RIGHT to manipulate text, such as extracting substrings from larger text. Similarly using IFERROR or ISERROR to manage instances where no results are found and an error is otherwise returned.
Examples
=SEARCH("fox", "The quick brown fox jumps over the lazy dog")
Output: 17
This formula would return the position of the first occurrence of the text string “fox” within the larger text string “The quick brown fox jumps over the lazy dog”, which would be 17 (since the first character in the text string is the 17th character in the larger target text string).
You would get a similar result using the capitalized search text “Fox” or “FOX” etc., with the SEARCH function.
Case Sensitive Search
Use FIND for case sensitive search.
As before, the following example, using FIND to identify the positional occurrence of “fox” in the target string would return the result, 17.
=FIND("fox", "The quick brown fox jumps over the lazy dog")
Output: 17
However, the following example using FIND would return an #VALUE error, because the text string “FOX” is not found within the target string “The quick brown fox jumps over the lazy dog”
=FIND("FOX", "The quick brown fox jumps over the lazy dog")
Output: #Value!
Combining Functions
Instead of passing the text character you wish to find, the ASCII character equivalent can be passed, CHAR(65) represents the text character “A”, and CHAR(97) represents the text character “a”.
=SEARCH(CHAR(65), "A B C D")
Output: 1
As before,
=SEARCH(CHAR(97), "A B C D")
Output: 1
In this formula, the SEARCH function searches for the letter “A” (ASCII value 65) within the binary string “A B C D”. Since the letter “A” is found at the first position in the string, the function returns 1. Similarly the output 1 is returned for CHAR(97). However when the function FIND is used instead of SEARCH the use of CHAR(97) results in an #Value error (not found)
Wild Card Characters
You can use the wildcard characters in the find_text argument.
Both
- the question mark (?) – for single character wild cards, and
- the asterisk (*) – for multiple character wild cards
=SEARCH("?ox", "The quick brown fox jumps over the lazy dog")
Output: 17
In the case above a single character wild card means the match is again at position 17,
Whereas when a * wildcard is used, the search will include all characters which match the search term, returning the position of the first found match.
=SEARCH("*ox", "The quick brown fox jumps over the lazy dog")
Output 1
Related Topics
- Other Excel Functions
- Other Excel Text Manipulation Functions
F010