SEARCH

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

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.

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.