Excel Function: FIND

Purpose

The FIND function searches for and returns the location of one text value in another text value. The search is case sensitive, traverses the text value being searched from left to right and begins from the character whose position matches the value passed to the start_num argument. It ends either (1) when the first instance of the text value being searched for is discovered, or (2) the end of the text value being searched is reached. Upon discovering the first instance of the text value being searched for, the FIND function returns a whole number whose value is ≥ 1, which represents the position of its first character in the text value that was searched. #VALUE! is returned if the search fails to discover any instances of a text value being searched for.

Invocations of the FIND function take the form:

FIND(find_text,within_text,[start_num])

Limitations

Wildcard characters are not supported.

Example

Consider a cell containing the formula:

=FIND("abc","ABC abc ABC abc",9)

The formula instructs the FIND function to search the text value “ABC abc ABC abc” for instances of “abc” and to begin its search from the ninth character (“A“). Starting with this character, 3 characters are retrieved (“ABC“). Since the cases of “ABC” and “abc” differ, they do not match and another 3 characters are retrieved starting from the tenth character (“BC “) and they are compared to “abc“. This process of retrieving 3 characters and comparing them to “abc” repeats until either a match is found or it isn’t possible to retrieve 3 characters. Each time the process repeats, the starting character is moved one position to the right. The complete process is illustrated below:

Step 1, FIND advances to the 9th character in “ABC abc ABC abc” and extracts characters 9, 10 & 11 which form the text value “ABC” - this does not match “abc”. Step 2, FIND advances to the 10th character in "ABC abc ABC abc" and extracts characters 10,11 & 12 which form the text value "BC " - this does not match “abc”. Step 3, FIND advances to the 11th character in "ABC abc ABC abc" and extracts characters 11, 12 & 13 which form the text value "C a" - this does not match “abc”. Step 4, FIND advances to the 12th character in "ABC abc ABC abc" and extracts characters 12,13 & 14 which form the text value " ab" - this does not match “abc”. Step 5, FIND advances to the 13th character in "ABC abc ABC abc" and extracts characters 13,14 & 15 which form the text value "abc" - this matches “abc”. Step 6, the number 13 is returned by the FIND function, because this is the position of the first character in the "abc" located by the FIND function.

Arguments

Argument	Argument type	Description
find_text	Mandatory	A text value that will be searched for in the value supplied to the within_text argument.
within_text	Mandatory	A text value searched for instances of the value supplied to the find_text argument.
start_num	Optional. Defaults to 1 if omitted.	The position of the character in within_text from which the search for find_text will begin.

Related Functions

Function	Example	Purpose
SEARCH	`SEARCH(find_text,within_text,[start_num])`	Its purpose is identical to that of the FIND function: the SEARCH function searches for and returns the location of one text value in another text value. SEARCH differs from FIND in that it performs case-insensitive searches and supports wildcards.

Error Conditions

Error value	Condition type	Condition
#VALUE!	Bad argument	The value supplied to the start_num argument was ≤ 0.
#VALUE!	Bad argument	The value of start_num was greater than the number of characters in within_text.
#VALUE!	Runtime	The text value find_text could not be found in within_text.

Examples

Example	Formula	Result	Description
1	`=FIND("abc", "abc abc",1)`	`1`	Find the first instance of “abc” that occurs from character 1 onwards in “abc abc“. The search begins from character 1 of “abc abc“. Characters 1 – 3 match the text value being searched for. The FIND function returns the number 1.
2	`=FIND("abc", "abc abc",2)`	`5`	Find the first instance of “abc” that occurs from character 2 onwards in “abc abc“. The search begins from character 2 of “abc abc“. Characters 5 – 7 match the text value being searched for. The FIND function returns the number 5.
3	`=FIND("","abc",2)`	`2`	Find the first instance of “” (the empty text value) that occurs from character 2 in “abc“. The search begins from character 2 of “abc” (b). The empty text value between “b” and “c” matches the empty text value. The FIND function returns the number 2. This demonstrates that: FIND treats the empty text value as a character. The position of the non-empty character preceding “” is returned.

Examples: Errors

Example	Formula	Result	Description
1	`=FIND("abc","abcabc",0)`	`#VALUE!`	Find the first instance of “abc” that occurs from character 0 onwards in “abcabc“. Because no character can exist at position 0 in a text value, #VALUE! is returned.
2	`=FIND("a","aaaaa",6)`	`#VALUE!`	Find the first instance of “a” that occurs from character 6 onwards in “aaaaa“. Because no character 6 exists in “aaaaa”, #VALUE! is returned.
3	`=FIND("z","abc",1)`	`#VALUE!`	Find the first instance of “z” that occurs from character 1 onwards in “abc“. Because “z” does not exist in “abc”, #VALUE! is returned.
4	`=FIND("abc", "Abc abC",1)`	`#VALUE!`	Find the first instance of “abc” that occurs from character 1 onwards in “Abc abC“. Because FIND is case sensitive, “abc” fails to match either “Abc” or “abC“. #VALUE! is returned.