Function Reference: FIND, SEARCH
| Syntax | ||
| =FIND(find_text,within_text,[start_num]) =SEARCH(find_text,within_text,[start_num]) |
||
| Purpose / Description | ||
| Returns the location of the a sub-string within another string, providing the character position of the first character. FIND is case sensitive whereas SEARCH ignores the case of the text strings, doing a case-insensitive search. | ||
| Arguments | ||
| Name | Type | Description |
| find_text | text string | A constant, reference or nested expression that provides the text string to be found within another string. |
| within_text | text string | A constant, reference or nested expression that provides the text string within which the sub string is to be located. |
| [start_num] | number | An optional constant, reference or nested expression that provides the character position (counting from 1 for the first character) from which the search commences. Defaults to 1 if omitted. |
| Return Values | ||
| Type | Specific Value | Description |
| number | 1 | If the find_text argument is an empty string, even if the within_text argument is an empty string, the position 1 is returned by both functions. |
| number | The position of the first character of the text string passed to find_text within the text string passed to within_text. | |
| error | #VALUE! | If the text string in the find_text argument cannot be located within the text string in within_text. In the case of the FIND function, this may be caused by case mismatches between the strings. |
| error | #VALUE! | If the value passed to start_num is not a number and cannot be coerced to a number |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Note: | The only difference between the two functions is that SEARCH is case insensitive, whereas FIND requires the case of the sub strings to match. | |
| Advice: | If case matching is unimportant, use the SEARCH function to avoid case matching problems. | |
| Caution: | Nesting the FIND and SEARCH inside an IFERROR can handle unmatched searches, however, other errors can be masked. | |
| Examples: | Open / download sample workbook: FIND, SEARCH.xlsx | |