SEARCH
Returns the position of the specified search string, starting at an optional start position. Start index can be negative. Allows the wildcard characters ? (to match any single character) and * (to match one or more characters). A ~ before the ? or * character (e.g. "~?" or "~*") matches the specific character "?" or "*".
Syntax
=SEARCH(needle, haystack, start)
Arguments
| Argument | Type | Description |
|---|---|---|
| needle | String | The string to find. The string can contain the wildcard characters ? or * |
| haystack | String | The string to search in |
| start | Number | (Optional) The index in the haystack to start searching. Defaults to 1 |
Start is 1-based (i.e. an index of 1 is used for the first character). Negative indexes are allowed and index from the end of the string (i.e. an index of -1 gets the last character).
Examples
=SEARCH("A?C", "ABCDEF")→1
Finds the string "A?C" ("A", followed by any character, followed by "C") in the string "ABCDEF" (starting at character 1, because the index was not specified). Since the string starts with "ABC", returns 1
=SEARCH("A?C", "ABCDEF", 2)→None
Finds the string "A?C" ("A", followed by any character, followed by "C") in the string "ABCDEF" starting at character 2. The string "A?C" is not found starting at character 2, because the find function is searching within "BCDEF"
=SEARCH("XYZ", "ABCDEF")→None
Finds the string "XYZ" in the string "ABCDEF" (starting at character 1, because the index was not specified). The string "XYZ" is not found in the string, so this returns None
=SEARCH("X*X","XXXXXX")→1
Finds the string "X_X" ("X" followed by one or more characters, followed by "X") in the string "XXXXXX" (starting at character 1, because the index was not specified). The string "X_X" is found multiple times in the string, so the first match is returned (1)
=SEARCH("Z","ABCDEFGHIJKLMNOPQRSTUVWXYZ")→26
Finds the string "Z" in the string "ABCDEFGHIJKLMNOPQRSTUVWXYZ" (starting at character 1, because the index was not specified).
=SEARCH("Y", "XYZXYZ", -3)→5
Finds the string "Y" in the string "XYZXYZ" starting at the 3rd character from the end
Updated 7 months ago