Help Center

SEARCH

Formula Explanation

The SEARCH function is used to find the first text string within the second text string and returns the position number of the first text string's start, counting from the first character of the second text string.

For example, to find the position of the letter "n" in the word "printer," you can use the following formula:

SEARCH("n","printer")

This function will return 4 because "n" is the fourth character in the word "printer."

You can also search for one word within another word. For example, the following formula:

SEARCH("base","database")

will return 5 because the word "base" starts from the fifth character of the word "database."

The SEARCH function is useful for determining the position of a character or text string within another text string, and you can then use MID and MIDB functions to return text or REPLACE and REPLACEB functions to change text. Example 1 in this document demonstrates the use of these functions.

Formula Syntax

Enter in the result column: SEARCH(find_text, within_text, [start_num])

The SEARCH function has the following arguments:

  • find_text (required): The text you want to find.

  • within_text (required): The text in which you want to search for the find_text argument's value.

  • start_num (optional): The character number in within_text from which to begin the search for the find_text argument's value.

Notes

  • SEARCH function is not case-sensitive. If you want a case-sensitive search, you can use the FIND and FINDB functions.
  • Wildcards (question mark (?) and asterisk (*)) can be used in the find_text argument.
    • The question mark matches any single character, and the asterisk matches any sequence of characters. To find the actual question mark or asterisk, type a tilde (~) before the character.
  • If start_num is omitted, it is assumed to be 1.
  • You can use start_num to skip a specified number of characters.
    • For example, with the SEARCH function, if you want to find the position of the first "Y" in the description part of a text string "AYF0093.YoungMensApparel," you can set start_num to 8 so that it doesn't search the serial number part (i.e., "AYF0093") of the text. The SEARCH function starts at the 8th character, looks for the character specified in the find_text argument, and returns the number 9. The SEARCH function always returns the character number counted from the beginning of the within_text argument, taking into account any skipped characters if the start_num argument is greater than 1.

Usage Example

  1. In column EF2, find the position of the letter "d," and write the result in column EF3.
EF3 = SEARCH("d", EF2)

search

  1. Find the position of "margin" (the string to search for) within "Profit Margin" (the string to search in) = 8
=SEARCH("Margin", "Profit Margin")
  1. Return the first four characters after the first space character in "Profit Margin" (cell "Profit Margin") = "Marg"
=MID("Profit Margin", SEARCH(" ", "Profit Margin") + 1, 4)
  1. Find the position of the first double quotation mark (") in the cell "The ""boss"" is here." = 5
=SEARCH("""", "The ""boss"" is here.")
Last Updated: