FIND function

FIND(SearchText, MasterText, StartIndex?) FIND(SearchText; MasterText; StartIndex?)

SearchText

Text or { Text }

The text string to find.

MasterText

Text or { Text }

The search text, that is, the text string within which the searchText text string may be present.

StartIndex

Number or { Number } (optional)

The position in the masterText text string where the search begins. If omitted, the search begins at the beginning of the text string. The first character is at position 1.

Returns

Number or { Number }

The position where the searchText text string is found within the masterText text string, or an error if the text string cannot be found. The first character is at position 1.

Finds a text string nestled within another text string and returns its position. FIND("st", "test")FIND("st"; "test") returns 3, because "st" starts at the third character in "test".

FIND returns an error if the text string cannot be found. ISERROR may be used to determine if a function returns an error. As such, this formula returns TRUE to indicate that a text string cannot be found:

ISERROR(FIND("abc", "test"))ISERROR(FIND("abc"; "test"))

However, it is simpler to use CONTAINS to determine if a text string contains another text string. This formula returns FALSE:

CONTAINS("test", "abc")CONTAINS("test"; "abc")

FIND is case-sensitive, meaning that this formula returns TRUE, indicating that the text string cannot be found:

ISERROR(FIND("st", "TEST"))ISERROR(FIND("st"; "TEST"))

Use SEARCH instead if you need to perform a case-insensitive match or need support for wildcards (where "a*e" matches text strings like "abcde", "ae", "aqqe", etc). Use REGEXMATCH instead if you need more powerful matching than wildcards can provide.

Examples

FIND("st", "test")FIND("st"; "test")

Returns 3, because "st" starts at position 3 in "test".

FIND("sT", "test")FIND("sT"; "test")

Returns an error. "sT" is not found within "test", because FIND is case-sensitive.

FIND("test", "testing testing")FIND("test"; "testing testing")

Returns 1, because "test" starts at position 1.

FIND("test", "testing testing", 3)FIND("test"; "testing testing"; 3)

Returns 9, because "test" starts at position 9. The start position has been set to 3, meaning that FIND does not find "test" which starts at position 1, but rather "test" which starts at position 9.

FIND("test", TextField1)FIND("test"; TextField1)

Returns the position where "test" is found within the value of TextField1, or returns an error if it cannot be found.

ISERROR(FIND("test", TextField1))ISERROR(FIND("test"; TextField1))

Returns TRUE if "test" cannot be found within the value of TextField1.

CONTAINS(TextField1, "test")CONTAINS(TextField1; "test")

Returns FALSE if "test" cannot be found within the value of TextField1.

FIND({ "st", "e" }, "test")FIND({ "st"; "e" }; "test")

Returns the array { 3, 2 }{ 3; 2 }, because "st" starts at position 3 in "test" and "e" starts at position 2.

Learn

Blog

Pricing