FIND Function in Excel

The FIND function of Excel searches and returns the position of a character within a text string. This position is returned as a numeric value, which represents the first instance of such character. The FIND function can also be informed the exact position from where the search should begin.

For example, the formula =FIND(“w”,“sunflower”) returns 7. This implies that the character “w” is at the seventh position of the text string “sunflower.”

The FIND function searches within the text string beginning from left to right. The purpose of using the FIND function is to ascertain whether a particular substring occurs in a specific cell or not. The FIND can also be used with other Excel functions to extract certain characters of a text string.

The FIND function is categorized as a Text function of Excel.

The syntax of the FIND function of Excel is shown in the following image:

The FIND function of Excel accepts the following arguments:

• Find_text: This is the character (or substring) whose position is to be searched. It can be supplied as a reference to a cell containing the substring or as a direct substring enclosed within double quotation marks.  
• Within_text: This is the text string within which the character (or substring) needs to be searched. It can be supplied either directly to the FIND function or as a reference to a cell containing the text string. Enclose the text string within double quotation marks when supplied directly to the function.
• Start_num: This is the character from which the search shall begin. It is supplied as a numeric value to the FIND function. For instance, if this argument is 3, the search begins from the third character of the “within_text” argument.

The arguments “find_text” and “within_text” are required, while “start_num” is optional. If the “start_num” argument is omitted, the search begins from the first character of the “within_text” argument.

Let us consider some examples to understand the working of the FIND function in Excel.

Example #1–Find a Single Character Within a Text String

The following image shows a text string and substring in cells A3 and B3 respectively. Within the text string (leopard), we want to find the substring (a) by using the FIND function of Excel. Supply the “find_text” and “within_text” arguments as:

1. Cell references
2. Direct strings

Consider the “start_num” argument to be 1 in both cases.

a. The steps to use the FIND function with cell references are listed as follows:

Step 1: Supply the cell references of the substring and the text string (in the stated sequence) to the FIND function. So, enter the following formula in cell C3.

“=FIND(B3,A3)”

Notice that the “start_num” argument is omitted since it is 1.

Step 2: Press the “Enter” key. The output appears, as shown in the following image. Hence, the character “a” is the fifth letter of the word “leopard.”

b. The steps to use the FIND function with direct strings are listed as follows:

Step 1: Supply the substring and the text string (in the stated sequence) to the FIND function directly. So, enter the following formula in cell C4.

“=FIND(“a”, “Leopard”)”

Since the “start_num” argument is 1, it has been omitted.

Step 2: Press the “Enter” key. The output in cell C4 is 5. This is shown in the following image. Hence, whether the first two arguments are entered as cell references or direct strings, the output of the FIND function is the same.

Example #2–Count the Number of Times a Single Character Occurs in a Range

The following image shows some random text strings in the range A3:A6. We want to find the number of times the character (or substring) “i” appears in this range. Use the FIND, ISNUMBER, and SUMPRODUCT functions of Excel.

The steps to find the character count by using the stated functions are listed as follows:

Step 1: Enter the following formula in cell B3.

“=SUMPRODUCT(- -(ISNUMBER(FIND("i",A3:A6))))”

Step 2: Press the “Enter” key. The output in cell B3 is 3. This is shown in the following image. Hence, the character “i” appears thrice in the range A3:A6.

Explanation: In the formula entered in step 1, the FIND function is processed first as it is the innermost function. Next, the ISNUMBER and SUMPRODUCT functions are processed. The entire formula works as follows:

• The FIND function looks for the character “i” in each cell of range A3:A6. This character is found in cells A3, A4, and A6. It is not found in cell A5. If the character “i” is found in a cell, the FIND function returns its position. However, if this character is not found in a cell, the FIND function returns the “#VALUE!” error. So, the FIND function returns an array of four values {3;5;#VALUE!;5 }.
• The array returned by the FIND function becomes an argument of the ISNUMBER function. If the output of the FIND function is numeric, the ISNUMBER returns “true.” However, if the output of the FIND function is an error, the ISNUMBER function returns “false.” So, the ISNUMBER returns an array of Boolean values {TRUE;TRUE;FALSE;TRUE}.
• The unary operator or the double negative symbol (- -) converts every “true” and “false” output of the ISNUMBER function into 1 and 0 respectively. So, the unary operator returns a vertical array of four numbers {1;1;0;1}.
• The array ({1;1;0;1}) returned by the unary operator becomes an argument of the SUMPRODUCT function. Since it is a single array of numbers, the SUMPRODUCT sums it. So, the sum returned by the SUMPRODUCT function is 3.

Hence, the final output of the given formula (entered in step 1) is 3.

Notice that three cells of the range A3:A6 contained a single instance of the character “i.” However, had there been multiple occurrences of this character in cells A3, A4 or A6, the FIND function would have returned its first instance. Therefore, the final output would still have been 3.

Note 1: The ISNUMBER function checks whether a value (or a cell containing a value) is numeric or not. The SUMPRODUCT function multiplies the numbers of two or more arrays and sums up the resulting products. In case of a single array, the SUMPRODUCT function adds the numbers and returns their sum.

For the syntax of the ISNUMBER and SUMPRODUCT functions, click the hyperlinks given in the preceding explanation.

Note 2: Rather than the FIND function, one could have used the COUNTIF function. The formula for counting the character “i” in the range A3:A6 would be =COUNTIF(A3:A6,“*i*”). This formula would also have returned the output 3.

Like the FIND function, the COUNTIF would also have returned 3 in case of multiple occurrences of character “i” in cells A3, A4 or A6. However, the COUNTIF is not case-sensitive, unlike the FIND function.

Example #3–Count the Text Strings Ending With Certain Characters

The following image shows a list of names in column A. We want to find the number of names ending with “ansh” or “anka” in the range A3:A10. Use the FIND, ISNUMBER, and SUMPRODUCT functions of Excel.

The steps to count specific names by using the stated functions are listed as follows:

Step 1: Enter the following formula in cell B3.

“=SUMPRODUCT(- -((ISNUMBER(FIND("ansh",A3:A10))+ISNUMBER(FIND("anka",A3:A10)))>0))

Step 2: Press the “Enter” key. The output in cell B3 is 4. This is shown in the following image.

Hence, a total of 4 names end with “ansh” or “anka.” Two names end with the former substring (Priyansh and Divyansh) and two names end with the latter substring (Priyanka and Divyanka).

Explanation: The formula entered in step 1 is explained as follows:

• The FIND function processes each name of the range A3:A10. If “ansh” or “anka” are present in a cell, the FIND function returns the position of the first letter of these substrings. However, if these substrings are not present in a cell, the FIND function returns the “#VALUE!” error. So, the FIND function returns two arrays {#VALUE!;#VALUE!;5;5;#VALUE!;#VALUE!;#VALUE!; #VALUE!} and {5;5;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}.
• The ISNUMBER processes the outputs returned by the FIND function. If the output of the FIND function is numeric, the ISNUMBER returns “true,” otherwise returns “false.” So, the ISNUMBER function returns two arrays {FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE} and {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• The two arrays returned by the ISNUMBER function are added due to the “plus” operator placed between them. The array returned after addition and the application of the “greater than zero” expression is {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}.
• The unary operator (- -) converts each “true” and “false” output of the preceding array to 1 and 0 respectively. So, the unary operator returns the final array of 8 values {1;1;1;1;0;0;0;0}.
• The SUMPRODUCT function sums the single array returned by the unary operator. Hence, this function returns the output 4.

Note 1: The “greater than zero” expression checks whether each output of the two arrays (returned by the ISNUMBER function) is greater than zero or not. This expression is useful in case one requires logical values (true and false) as the output.

This expression returns “true” if either of the two outputs (of the two arrays returned by the ISNUMBER function) is a number. It returns “false” if both outputs are errors.

Note 2: For knowing more about the ISNUMBER and SUMPRODUCT functions, refer to the hyperlinks (within the explanation) or “note 1” of the preceding example (example #2).

Example #4–Extract Certain Characters From Each Cell of a Range

The following image shows some incomplete statements containing a hash symbol (#) in column C. From each statement, we want to extract this symbol, the word following it, and the trailing space in a separate column. Use the MID and FIND functions of Excel.

The steps to extract the given substrings using the stated functions are listed as follows:

Step 1: Enter the following formula in cell D3.

 “=MID(C3,FIND("#",C3),FIND(" ",(MID(C3,FIND("#",C3),LEN(C3)))))”

Step 2: Press the “Enter” key. The output in cell D3 is “#Wedding .” It includes a space at the end. The output is shown in the following image.

Step 3: Drag the formula of cell D3 till cell D5 by using the fill handle. The output of the range D3 to D5 is shown in the following image.

Hence, the hash symbol, the following word, and the trailing space have been extracted from each statement of column C.

Explanation: In the given formula (entered in step 1), each function on the right is processed, followed by the subsequent function on the left. The LEN function, being the innermost (or rightmost), is processed first. The entire formula works as follows:

• The LEN function counts the total number of characters in cell C3. So, the formula “=LEN(C3)” returns 27. This count includes 23 letters, 3 spaces, and 1 hash symbol.
• Next, the FIND function searches the position of the hash symbol in cell C3. So, the formula “=FIND(“#”,C3)” returns 10. This implies that the hash symbol is at the tenth position of the statement given in cell C3.
• The outputs of the FIND and LEN functions become the arguments of the MID function. So, the MID function processes the formula “=MID(C3,10,27)” and returns “#Wedding in Jaipur.”
• The output of the MID function becomes the argument of the FIND function. The FIND function searches for a space character in the string “#Wedding in Jaipur.” So, the formula “=FIND(“ ”,“#Wedding in Jaipur”) returns 9. This implies that the first instance of the space character is at the ninth position of the given text string (#Wedding in Jaipur).
• The formula “=FIND(“#”,C3)” again returns 10.
• The outputs of the two FIND functions (in the preceding two pointers) become the arguments of the MID function. The formula “=MID(C3,10,9)” returns the string beginning from the tenth character and consisting of nine characters in total. So, this formula returns “#Wedding ” including a single space at the end of the word.

Likewise, the outputs in cells D4 and D5 have been returned by Excel.

Note 1: The LEN function counts all the characters in a cell. This includes letters, numbers, spaces, and special characters. The MID function helps extract a certain number of characters from the middle of the string. The position to begin extraction from can be specified to the MID function.

For the syntax of the LEN and MID functions, click the hyperlinks given in the preceding explanation.

Note 2: In the formula “=MID(C3,10,27),” the “start_num” argument is 10 and the “num_chars” argument is 27. If the sum of these two arguments exceeds the total length of the string, the MID function returns the characters beginning from the “start_num” till the end of the entire string.

Therefore, since 37 (“start_num” is 10 and “num_chars” is 27) exceeds 27 (length of the entire string), the MID function returns the character beginning from the tenth place (#) till the end of the string (Jaipur). So, the MID function returns “#Wedding in Jaipur.”

The FIND function of Excel is helpful in the following situations:

• It helps extract the relevant characters, thereby removing the unwanted substrings of a text string.
• It helps extract the words preceding or succeeding a specific character.
• It assists in searching the n^th occurrence of a character.
• It can be combined with other functions of Excel to find the number of times a character appears in a range.

The important points related to the FIND function of Excel are listed as follows:

• The FIND function looks for the first occurrence of the “find_text” in the “within_text” argument.
• The “find_text” and “within_text” arguments can be supplied as cell references or direct text strings. To supply these arguments as direct text strings, they must be enclosed within double quotation marks.
• If the “find_text” argument contains more than one character, the FIND function returns the position of the first character in the “within_text” argument.
• If the “find_text” argument is an empty string (“”), the FIND function returns 1.
• The FIND function is case-sensitive and does not support the usage of wildcard characters.
• The FIND function returns the “#VALUE!” error in the following cases:
  • The “find_text” is not found in the “within_text” argument
  • The “start_num” argument is zero or negative
  • The “start_num” argument contains more characters than the “within_text” argument