SEARCH

SEARCH

Returns the number of the character at which a specific character or text string is first found, reading from left to right. Use SEARCH to discover the location of a character or text string within another text string, so that you can use the MID or REPLACE functions to change the text.

Syntax

SEARCH(find_text,within_text,start_num)

Find_text   is the text you want to find. You can use the wildcard characters, question mark (?) and asterisk (*), in find_text. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. If find_text is not found, the #VALUE! error value is returned.

Within_text   is the text in which you want to search for find_text.

Start_num   is the character number in within_text, counting from the left, at which you want to start searching.

  • If start_num is omitted, it is assumed to be 1.
  • If start_num is not greater than 0 (zero) or is greater than the length of within_text, the #VALUE! error value is returned.

Tip   Use start_num to skip a specified number of characters from the left of the text. For example, suppose you are working with a text string such as "AYF0093.YoungMensApparel". To find the number of the first "Y" in the descriptive part of the text string, set start_num equal to 8 so that the serial-number portion of the text is not searched. SEARCH begins with character 8, finds find_text at the next character, and returns the number 9. SEARCH always returns the number of characters from the left of the text string, not from start_num.

Remarks

  • SEARCH does not distinguish between uppercase and lowercase letters when searching text.
  • SEARCH is similar to FIND, except that FIND is case-sensitive.

Examples

SEARCH("e","Statements",6) equals 7

If cell B17 contains the word "margin", and cell A14 contains "Profit Margin", then:

SEARCH($B$17,$A$14) equals 8

Use SEARCH with the REPLACE function to provide REPLACE with the correct start_num at which to begin inserting new text. Using the same cell references as the previous example:

REPLACE($A$14,SEARCH($B$17,$A$14),6,"Amount") returns the text "Profit Amount"