The SEARCH function returns the position of the searched text within the viewed text.
Counts double-byte characters as 1 character.
The function is case insensitive, supports wildcards “?” and “*”.
This function can be useful when you need to determine whether a specific substring is contained in a string and find its position.
Syntax
SEARCH(find_text, within_text, [start_num])
Argument |
Description |
Permitted values |
|---|---|---|
find_text |
String to be found in the “within_text” argument |
Text string or reference to a cell containing text |
within_text |
Text in which the search needs to be carried out |
Text string or reference to a cell containing text |
[start_num] |
(optional) Position of the character in the “find_text” argument from which to start the search. By default, 1 |
Integer ≥1 or a reference to a cell containing a number |
Examples of use
Search for a substring
If cell A1 contains the text “I love apples” and you want to find the position of the word “love”:
=SEARCH("love", A1)
The expression will return 3, since “love” starts with the third character.
Search using wildcards
=SEARCH("@?????.", A2)
This formula will return the position of the “@” symbol in cell A2, followed by exactly 5 characters of any kind, and then a period.
An example where the find_text argument is not found in the within_text argument
If you are searching for text that is not contained in the string, for example:
=SEARCH("cat", A1)
The expression will return the #VALUE! error, since the fragment “cat” is not found in the string.
Notes
–The SEARCH function is not case-sensitive, meaning that “Tables” and “tables” will be treated as identical strings.
–If the value of the start_num argument exceeds the length of the within_text argument, the function will return the #VALUE! error.
–This function can be particularly useful in combination with other functions, such as LEFT, RIGHT, or SUBSTITUTE, for more complex text manipulations.
–Ensure that the find_text and within_text arguments are specified in the correct order.