SEARCH

Returns the position of one text string within another text string. The returned position is one-based. If no match is found, the function returns 0.

Syntax

SEARCH(find_text, within_text, [start_num])

Arguments

• find_text: The text or pattern to search for.
• within_text: The text string to search within.
• start_num: Optional. The character position at which to begin the search. The default is 1.

Return Value

Returns the position of the first matching character.

• The first character is position 1.
• Returns 0 if the text is not found.
• Returns 0 if start_num is less than 1, invalid, or beyond the end of the text.
• If find_text is empty, returns start_num.

Examples

SEARCH("DB", "AnyDB") → Returns 4
SEARCH("any", "AnyDB") → Returns 1
SEARCH("@anydb.com", "user@anydb.com") → Returns 5
SEARCH("a", "Banana", 3) → Returns 4
SEARCH("xyz", "AnyDB") → Returns 0

Wildcards

SEARCH supports Excel-style wildcard patterns:

• ? matches any single character.
• * matches any sequence of characters.
• ~ can be used to search for a literal wildcard character.

SEARCH("A*y", "AnyDB") → Returns 1
SEARCH("D?", "AnyDB") → Returns 4

Validation Example

SEARCH("@anydb.com", CURRCELL) > 0
  ? false
  : "Invalid Email, must be anydb.com"

This returns false when the email contains @anydb.com, indicating that validation passed. Otherwise, it returns the validation error message.