FIND Function in Excel

What Is FIND Function In Excel?

The FIND function in Excel is used to find the text string in any cell and returns the position of that cell as output. The Find function only takes text format as searched reference. It is the most underrated function of the “Formula” tab under the “Function Library” group.

Let us learn FIND function in Excel with example. Consider the table with the text string in column A. Now, we need to use the below steps to fetch the letter L’s position from the string ‘Excelmojo’ mentioned in column B using the FIND function.

The steps used to fetch the position of a letter in a text string using FIND function in Excel are as follows:

• To begin with, select the cell to display the result.
• Next, enter the FIND formula in cell C2. So, the complete formula is =FIND(B2,A2)
• Then, press Enter key.

Clearly, we can see that the result is returned as 5. It indicates the letter ‘l’ position in the text string.

Likewise, we can use the FIND function in Excel to fetch the letter’s position in the text.

Key Takeaways
• The FIND function in Excel is used to find the position of specified characters.
• The formula of FIND function is =FIND(find_text,within_text,[start_num]) where,
• find_text is the mandatory argument and denotes the substring or character we are looking to find
• within_text is also a mandatory argument and indicates the string that is to be searched within
• start_num is an optional argument and shows the position in the within_text string from which the search begins.
• The FIND function is case-sensitive. The SEARCH function is used to search without case sensitivity.
• However, the FIND function does not use special characters.

Find() Excel Formula

The syntax of the FIND Excel Function is:

where,

• find_text: It is a mandatory argument and denotes the substring or character we are looking to find.
• within_text: It is also a mandatory argument and indicates the string to be searched within.
• start_num: It is an optional argument and shows the position in the within_text string from which the search begins.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use FIND Excel Function?

1. Access from the Excel ribbon.

1. To begin with, go to the Formulas tab.

2. Next, click the Text functions under the Function Library

3. Then, choose the FIND function from the drop-down list.

4. The Function Arguments window opens.

5. Next, enter the values in the arguments in the Find_text, Within_text & Start_num box.

6. Click OK.

2. Enter the worksheet manually

• Select an empty cell for the output.
• Type =FIND( in the selected cell.

Alternatively, type =F and double-click the FIND function from the list of suggestions shown by Excel.

The below table shows the string and search character in columns A and B, respectively. First, we need to find the ‘l’ position using the Find function in Excel.

The steps used to fetch the position of the letter with the FIND function are as follows:

Step 1: To begin with, select an empty cell to display the output. We have selected cell C2 in this case.

Step 2: Next, start by entering the formula in cell C2.

Step 3: Now, enter the find_text value as B2 in cell C2.

Step 4: Then, enter the within_text value as A2 in cell C2.

So, the complete formula is =FIND(B2,A2).

Clearly, the searched character position is shown in C2 as ‘3’.

Likewise, we can use FIND function in excel.

Examples

Example – 1 – Find A Single Character Within A Text String

The following table shows the string and searching character in column A and B. Now, we need to use the below steps to find the position of ‘l’ using Find function in Excel.

The steps used to fetch the position of a letter in the text using FIND function are as follows:

Step 1: First, select an empty cell to display the output. We have selected cell C2 in this case.

Step 2: Next, start by entering the formula in cell C2.

Step 3: Then, enter the find_text value as B2 in cell C2.

Step 4: Now, enter the within_text value as A2 in cell C2.

So, the complete formula is =FIND(B2,A2).

The searched character position is shown in C2 as ‘4’.

Similarly, we can use the FIND function to fetch the position of a mentioned letter.

Example – 2 – Count The Number Of Times A Single Character Occurs In A Range

Let us learn FIND function in Excel with example.

The below table lists fruits in column A. Next, we need to count the number of times the letter ‘a’ occurs in the range using the FIND function in Excel with SUMPRODUCT and ISNUMBER Excel function.

The steps to find the character using FIND function are as follows:

Step 1: First, select an empty cell to display the output. We have selected cell B2 in this case.

Step 2: Next, start by entering the formula in cell B2.

Step 3: Then, enter the Excel SUMPRODUCT formula.

This function will return the unary operator, which becomes an argument of the SUMPRODUCT function.

Remember, the array of numbers is a single array; the SUMPRODUCT will sum it.

Step 4: The unary operator or the double negative symbol (- -) converts every ‘true’ and ‘false’ output of the ISNUMBER function into 1 and 0, respectively.

Step 5: Then, enter the ISNUMBER formula.

Also, this function will be returned by the FIND function and becomes an argument of the ISNUMBER function.

If the output of the FIND function is numeric, the ISNUMBER returns ‘true,’ and if the production of the FIND function is non-numeric or error, the ISNUMBER returns ‘false.’

Step 6: Next, enter the find_text value as ‘a’ in cell B2.

Step 7: Then, enter the within_text value as A2:A6 in cell B2.

So, the complete formula is =SUMPRODUCT(–(ISNUMBER(FIND(“a”,A2:A6)))).

Step 8: Finally, press Enter key.

Clearly, we can see that the function has returned the output as ‘5’. It indicates that the character ‘a’ has appeared in the table range for 5 times.

Likewise, we can detect the character’s position using FIND function in excel.

Example – 3 – Count The Text Strings Ending With Certain Characters

The following table shows text strings in column A. Now, we need to find the below steps to count the characters ending with ‘c’ and ‘p’ using the FIND function in Excel with SUMPRODUCT and ISNUMBER functions.

The steps to count the characters using the FIND function in Excel with SUMPRODUCT and ISNUMBER functions are as follows:

Step 1: To begin with, select an empty cell to display the output. We have selected cell B2 in this case.

Step 2: Next, start by entering the formula in cell B2.

Step 3: Then, enter the SUMPRODUCT formula.

This function will return the unary operator, which becomes an argument of the SUMPRODUCT function. The array of numbers is a single array; the SUMPRODUCT will sum it.

Step 4: The unary operator or the double negative symbol (- -) converts every ‘true’ and ‘false’ output of the ISNUMBER function into 1 and 0, respectively.

Step 5: Next, enter the ISNUMBER formula.

The FIND function will return this function and becomes an argument of the ISNUMBER function. If the output of the FIND function is numeric, the ISNUMBER returns ‘true,’ and if the product of the FIND function is non-numeric or error, the ISNUMBER returns ‘false.’

Step 6: Then, enter the find_text value as ‘c’ and ‘p’ in cell B2.

Step 7: Also, enter the within_text value as A2:A7 in cell B2.

So, the complete formula is =SUMPRODUCT(–((ISNUMBER(FIND(“c”,A2:A7))+ (ISNUMBER(FIND(“p”,A2:A7)))>0)).

Step 8: Now, press Enter key.

Clearly, the function has returned the number of times the text string ending with ‘c’ and ‘p’ in B2 as ‘6’.

Likewise, we can find the number of times a word has appeared using FIND function in excel.

Example – 4 – Extract Certain Characters From A Cell

The below table shows a text string in cell A2. Now, we will try to extract certain characters from the text string in cell B2.

Since we are looking to extract the characters after the “*” symbol from the middle of the text string present in cell A2, we will be using the FIND function in combination with MID function and LEN Excel function.

The steps to extract certain characters using the FIND function are as follows:

Step 1: Select an empty cell for the output. We have selected cell B2 in this case.

Step 2: Next, enter the above formula in cell B2.

=MID(A2,FIND(“*”,A2),FIND(” “,MID(A2,FIND(“*”,A2),LEN(A2))))

The reasons we use LEN and MID functions here are:

• The MID function checks the string from the middle of the text string.
• The LEN function will count the length of the text string.

Step 3: Enter the find_text value as “*”, as we seek to extract the characters starting with the “*” symbol.

Step 4: Enter the within_text value as A2.

Step 5: Finally, press Enter key.

We can find the result as ‘*someone’ in cell B2.

Explanation:

• The MID function is used to extract certain characters from the middle of the text string present in the cell A2.
• After extracting the certain character from the string, the LEN function will truncate the trailing spaces and strings in the column from the full length.

Likewise, to extract the “*” symbol, the words following it, and the trailing spaces, we can modify the above formula as:

=MID(A2,FIND(“*”,A2),LEN(A2))

Important Things To Note

• The FIND function in Excel helps find the text’s position in a string.
• The function finds if the find_text argument contains two same characters. If so, the function will return the first character’s position.
• The within_text argument of the FIND function in Excel identifies the text within the sample.
• The FIND function in Excel finds if find_text is the empty string, the function gives back the first character in the search string.
• The FIND function returns the #VALUE! Error. It occurs if the find_text value does not exist in within_text, if the start_num contains more characters than within_text, or if the start_num is zero or a negative number.

Why do we use the FIND function in Excel?

The FIND function in Excel returns the position of the text string inside another. If the search string occurs more than once, the FIND function returns the part of the first occurrence. The FIND function does not support special characters, and it is case-sensitive.

Let us learn FIND function in Excel with example. The below table shows the string and search character in columns A and B, respectively. First, we need to find the position of the search characters using the Find function in Excel.

The steps used to find the position of the characters with the FIND function are as follows:

Step 1: First, select an empty cell to display the output. We have selected cell C2 in this case.

Step 2: Next, start by entering the formula in cell C2.

Step 3: Then, enter the find_text value as B2 in cell C2.

Step 4: Also, enter the within_text value as A2 in cell C2.

So, the complete formula is =FIND(B2,A2).

The searched character position is shown in C2.

Similarly, we can find the position of characters using FIND function in excel.

FIND function in Excel not working?

The FIND function in Excel may not work due to the following reasons;

1. If the within_text argument does not contain the find_text argument value.
2. The value is case-sensitive.
3. If the start_text argument is greater than the within_text argument.
4. If the start_text argument is smaller than or equal to 0.

Where is the FIND function in Excel?

We can use the FIND function in excel with the following steps:

1. To begin with, select the cell which has the number.
2. Next, select the Formulas tab.
3. Then, click on the Text option.
4. Select the FIND option from the drop-down list.
5. The Function Arguments window pops up.
6. Next, enter the value in the find_text, within_text, and start_num as the number of arguments.
7. Click OK.