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. The FIND function in Excel 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’s position from the string mentioned in column B with the FIND function.

FIND function in Excel Intro

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

Step 1: To begin with, select the cell to display the result.

Step 2: Next, enter the FIND formula in cell C2. So, the complete formula is =FIND(B2,A2

Step 3: 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.

FIND function in Excel Intro Example

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 in excel.
  • 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.
  • Remember 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:

FIND function syntax

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.

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


    FIND function in Excel How to - 1

  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.


    How to - 1.2

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.

FIND function in Excel How to - 2

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.

FIND function in Excel Basic Example

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.

FIND function in Excel Basic Example.1

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

FIND function in Excel Basic Example.2

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

FIND function in Excel Basic Example.3

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

FIND function in Excel Basic Example.4

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.

Example 1

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.

Example 1.1

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

Example 1.2

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

Example 1.3

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

Example 1.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.

FIND function in Excel Example 2

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

Example 2.1

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.

Example 2.2

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.

FIND function in Excel Example 3

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

Example 3.1

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

Example 3.2

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

Example – 4 – Extract Certain Characters From Each Cell Of A Range

The below table shows a text string in column A. Now, we need to use the following steps to extract certain characters from each cell using the FIND function in Excel with MID and LEN excel function.

 Example 4

The steps to extract values using the FIND function 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: The MID function checks the string from the middle.

Step 4: Likewise, the LEN function will count the length of the string.

Step 5: Then, enter the find_text value as ‘*’ in cell B2.

Step 6: Next, enter the within_text value as A2 in cell B2.

So, the complete formula entered is =MID(A2,FIND(“*”,A2),FIND(” “,MID(A2,FIND(“*”,A2),LEN(A2)))).

Example 4.1

Step 7: Finally, press Enter key.

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

FIND function in Excel Example 4.2

Similarly, we can extract certain characters from each cell range.

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.

Frequently Asked Questions

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.

FAQ 1

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.

FIND function in Excel FAQ 1.1

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.

Download Template

This article must help understand the FIND function in Excel with its formula and examples. You can download the template here to use it instantly.

This has been a guide to FIND Function in Excel. Here we discuss how to use FIND formula with examples and downloadable excel template. You can learn more from the following articles – 

Reader Interactions

Leave a Reply

Your email address will not be published.