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.
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
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"