SEARCH
The SEARCH function returns the starting position of one string value within another, ignoring case and allowing wild cards.
SEARCH(search-string, source-string, start-pos)
search-string: The string value to find.
source-string: The string value to search.
start-pos: An optional number value specifying the position within the string at which the action should begin. start-pos must be greater than or equal to 1 and less than or equal to the number of characters in source-string.
Notes
wild cards are permitted in search-string. In search-string, use an * (asterisk) to match multiple characters or a ? (question mark) to match any single character in source-string. You can also use a ~ (tilde) to specify that the following character should be matched rather than used as a wild card.
Specifying start-pos permits you to begin the search for search-string within, rather than at the beginning of, source-string. This is particularly useful if source-string may contain multiple instances of search-string and you wish to determine the starting position of other than the first instance. If start-pos is omitted, it is assumed to be 1.
To have case considered in your search, use the FIND function.
Examples |
|---|
=SEARCH(“ra”, "abracadabra") returns 3; the first occurrence of the string “ra” starts at the third character in “abracadabra”. =SEARCH(“ra","abracadabra", 5) returns 10, the position of the first occurrence of the string “ra” when you start looking at position 5. =SEARCH("*lock", "capslock") returns 1, because the asterisk at the beginning of the search string matches all the characters before “lock”. =SEARCH("*lok", "capslock") returns an error, because the string “lok” does not exist in “capslock”. =SEARCH("?lock", "capslock") returns 4, because the question mark matches the one character immediately preceding “lock”. =SEARCH(“l*k", "capslock") returns 5, because the asterisk matches all the characters between the “l” and “k”. =SEARCH("~?", "Capslock on? No.") returns 12, because the tilde means to interpret the next character (the question mark) literally, not as a wildcard, and the question mark is the 12th character. =SEARCH(REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"), "Where does john@appleseed.com start?") returns 12, the starting position of the first email in the source string. |