SEARCH

Formulas / SEARCH
To find where a substring is within another string.
=SEARCH(find_text,within_text,[start_num])
  • find_text - the substring to find
  • within_text - the text to search in
  • start_num - [OPTIONAL] the starting position. Defaults to 1.

Examples

  • =SEARCH("p","apple") // returns 2

    The SEARCH function returns the position of a character within a string. In the example above, the substring "p" is found in the string "apple" at position 2. If the substring is not found, the function returns the #VALUE! error value.

  • Example: =SEARCH("the","The cat in the hat") // returns 1

    The SEARCH function can also be used to find the position of a substring within a string, starting at a specified position within the string. In the example above, the substring "the" is found in the string "The cat in the hat" at position 1, even though "The" and "the" do not match on case.

  • =SEARCH("z","apple") returns #VALUE!The SEARCH function can also be used to find out if a substring does not exist within a string after a specified position within the string. In the example above, the substring "z" is not found in the string "apple". As a result, the function returns the #VALUE! error value.


Summary

The SEARCH function is a useful tool for finding a specific text string within another text string. The function returns the numerical starting position of one string within another.

  • SEARCH supports wildcard characters, like the question mark (?) and asterisk (*).
  • SEARCH is not case-sensitive. To perform case-sensitive searches, use Sourcetable's FIND function, which is case-sensitive.
  • If the same text shows up in the search string, SEARCH returns the first position of text found in a search string.
  • The SEARCH function only works with text value arguments enclosed in double-quotes.
  • The start_num argument is optional and is set to 1 by default.

Frequently Asked Questions

What is the SEARCH function?
The SEARCH function is used to locate a text string within another text string. It returns the starting position of the first text string from the first character of the second text string. The starting position is returned as a number.
How do I use the SEARCH function?
To use the SEARCH function, you need to specify the following arguments:
  • Find_text – this is the text string that you want to find.
  • Within_text – this is the text string that you want to search in.
  • Start_num – this is the starting position within the text string that you want to search from. This argument is optional, and the default value is 1.
What are the wildcard characters that can be used in the SEARCH function?
The question mark wildcard character (?) can be used in the find_text SEARCH argument. The asterisk wildcard character (*) can be used in the find_text SEARCH argument. The tilde character (~) can be used before a question market or asterisk to find those characters.
What are the errors that can be returned by the SEARCH function?
SEARCH returns #VALUE! error if find_text is not found. SEARCH returns #VALUE! error if start_num is not greater than 0. SEARCH returns #VALUE! error if start_num is greater than the length of the within_text argument.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV