SEARCH function
SearchText
The text string to find. May contain the wildcards *,
? and ~.
MasterText
The master text, that is, the text string within which the
searchText text string may be present.
StartIndex
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
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
Returns 3, because "st" starts at position 3 in "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".
Returns 3, because "st" starts at position 3 in "test". "sT" is found within "test", because SEARCH is case-insensitive.
Returns 1, because "test" starts at position 1.
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.
Returns the position where "test" is found within the value of TextField1, or returns an error if it cannot be found.
Returns TRUE if "test" cannot be found within the value of TextField1.
Returns the array { 3, 2 }, because "st" starts at position
3 in "test" and "e" starts at position 2.