FIND(find_text,within_text,start_num)
FINDB(find_text,within_text,start_byte)
Definition. This function returns the starting position of one text string within another text string. The function is case-sensitive.
Use FINDB() for double-byte characters.
Arguments
find_text (required). The text you want to find.
within_text (required). The text string to be searched.
start_num/start_byte (optional). The character at which to start the search. The first character/byte has the value 1. If the argument is omitted, 1 is used.
Background. If you edit text, you often need to know the position of a certain character or string within the text; for example, you might need to remove or replace the characters. To do this, you use the functions FIND() or SEARCH().
Note
Unlike the SEARCH() and SEARCHB() functions, FIND() and FINDB() are case-sensitive and do not allow wildcard characters.
Use start_num to ensure that the search doesn’t start at the first character but skips a specified number of characters. For example, if you want to know the position of the first e in the last part of the string XLS2003_FormatCellProtection, use 9 for start_num to skip the first part of the string. FIND() starts at the ninth character and returns the sixteenth character.
Also note the following when working with this function:
If you search for an empty string (“”), the FIND() function returns the value
1or the value specified in start_num.The find_text argument cannot include placeholders. FIND() returns the
#VALUE!error if it cannot find the string.FIND() returns the
#VALUE!error if start_num is smaller than or equal to 0.FIND() returns the
#VALUE!error if start_num is greater than the length of the string.
Example. Assume that you have a string that is separated into two parts by an underscore. If you want to locate the uppercase S in the second part of the string and find its position in the string, you will first need to determine the position of the underscore to ensure that the first part is not searched. Then you can perform the search in the remainder of the string. If cell C19 contains the string XLS2003_FormatCellSecure, the formula
=FIND("S",C19,FIND("_",C19)+1)returns 19, because the S in the second part of the string is located in the nineteenth position (see Figure 8-4).
Figure 8-4. Cell E18 contains the formula =FIND("_",D18)+1, and the result is passed to the second argument.
If you had simply used
=FIND("S",C19)the formula would have returned 3, which is the position of the S in the first part of the string.