FIND

Returns the position of a string of text within another string.

Syntax:

FIND(findtext, texttosearch, startposition)

returns the character position of the first occurrence of findtext within texttosearch.

startposition (optional) is the position from which the search starts.

The search is case-sensitive. For case-insensitive search, see SEARCH().

The search will not use regular expressions. For searching with regular expressions, see SEARCH().

A failed search gives the #VALUE! error.

Example:

FIND("yo", "Yoyo")

returns 3. The search is case-sensitive.

FIND("cho", "choochoo"; 2)

returns 5.

FIND("xyz", "abcdef", 1)

returns #VALUE!.

NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.

IF( FIND("xyz", "abcdef", 1) , "Substring Present", "ERR: Missing Substring" )

returns #VALUE! which is not very useful, therefore we could use either ISERROR() or ISNUMBER() for example:

IF( ISERROR( FIND("xyz", "abcdef", 1) ) , "ERR: Missing Substring", "Substring Present" ).

returns "ERR: Missing Substring" ... allowing the IF() to function, and not propagating the error from the FIND() function.

IF( ISNUMBER( FIND("xyz", "abcdef", 1) ) , "Substring Present", "ERR: Missing Substring" ).

returns "ERR: Missing Substring" ... allowing the IF() to function, and not propagating the error from the FIND() function.

NOTE: In practice, it may be more maintainable to use ISNUMBER() to avoid negative logic, and it is more indicative of the evaluation desired: if the substring has a position, then ISNUMBER() is TRUE, else ISNUMBER() is FALSE.

Application:

The FIND function is a great tool for locating specific characters or strings within a text.

Scenario: You have a list of email addresses and need to extract the username from each address.

Let's use a hypothetical table named Employee_Contact to demonstrate this.

	Employee ID	Full Name	Email Address
	A	B	C
1	101	John Smith	john.smith@company.com
2	102	Jane Doe	jane.doe@company.com
3	103	Peter Jones	peter.jones@company.com
4	104	Sarah Johnson	sarah.johnson@company.com

We can use the FIND function to find the position of the "@" symbol in each email address. This position is crucial because the username is everything to the left of the "@" symbol.

The formula would look something like this:

FIND("@", [EmailAddress])

FIND is the function.
"@" is the text we are looking for.
[EmailAddress] is the cell or column where the search takes place.

The FIND function will return the starting position of the "@" symbol. The output for our table would be:

	Employee ID	Full Name	Email Address	FIND("@", Email Address)
	A	B	C	D
1	101	John Smith	john.smith@company.com	11
2	102	Jane Doe	jane.doe@company.com	9
3	103	Peter Jones	peter.jones@company.com	12
4	104	Sarah Johnson	sarah.johnson@company.com	14