SEARCH function

SEARCH(searchText, masterText, startIndex?) SEARCH(searchText; masterText; startIndex?)

searchText — Text or { Text }

The text string to find. May contain the wildcards *, ? and ~.

masterText — Text or { Text }

The master 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. SEARCH("st", "test")SEARCH("st"; "test") returns 3, because "st" starts at position 3in "test". This function is case-insensitive and supports wildcards (*, ? and ~, see below).

Use FIND instead if you don't need wildcard support or if you need to perform a case-sensitive match. Use REGEXMATCH instead if you need more powerful matching than wildcards can provide.

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

Wildcards

Unlike FIND, this function supports wildcards, enabling flexible matching. Wildcards are special characters which match multiple text strings.

? matches any character, meaning that SEARCH("ca?", "cat")SEARCH("ca?"; "cat") and SEARCH("ca?", "car")SEARCH("ca?"; "car") both return 1, because "ca?" successfully matches both "cat" and "car". (It also matches text strings like "cab", "can" and "cap".) However, ca? does not match "card", because that word has four characters, and the ca? pattern only matches words with three characters.

* also matches any character, but it can also match zero characters or many characters. SEARCH("ca*", "card")SEARCH("ca*"; "card"), SEARCH("ca*", "cat")SEARCH("ca*"; "cat"), SEARCH("ca*", "ca")SEARCH("ca*"; "ca") all return 1, because "ca*" successfully matches "card", "cat" and "ca".

? and * can be combined in the same pattern. Colo*r ad*s are great? matches both "Color ads are great!" and "Colour adverts are great?".

Sometimes, the special characters ? and * need to be included as-is, and not have special meaning. To achieve that, precede each special character with a tilde ("~"). SEARCH("ca~*", "card")SEARCH("ca~*"; "card") returns an error, because "ca~*" does not match "card". However, SEARCH("ca~*", "ca*")SEARCH("ca~*"; "ca*") returns 1, because "ca~*" matches "ca*".

In other words, to search for a verbatim "*" character, write "~*". To search for a verbatim "?" character, write "~?". Finally, to search for a verbatim "~" character, write "~~".

Examples

SEARCH("st", "test")SEARCH("st"; "test")

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

SEARCH("s?", "test")SEARCH("s?"; "test")

Returns 3, because "st" starts at position 3 in "test". SEARCH supports wildcards, enabling "s?" to match any two-character text string starting with "s", including "st".

SEARCH("sT", "test")SEARCH("sT"; "test")

Returns 3, because "st" starts at position 3 in "test". "sT" is found within "test", because SEARCH is case-insensitive.

SEARCH("test", "testing testing")SEARCH("test"; "testing testing")

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

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

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

SEARCH("test", TextField1)SEARCH("test"; TextField1)

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

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

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

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

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