**What Is FIND Function In Excel?**

The

FIND function in Excelis 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.

##### Table of contents

###### 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,is the mandatory argument and denotes the substring or character we are looking to find*find_text*is also a mandatory argument and indicates the string that is to be searched within*within_text*is an optional argument and shows the position in the within_text string from which the search begins.*start_num*

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

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

**To begin with, go to the Formulas tab.****Next, click the Text functions under the Function Library****Then, choose the FIND function from the drop-down list.****The Function Arguments window opens.****Next, enter the values in the arguments in the Find_text, Within_text & Start_num box.****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.

- Enter the arguments as cell references in Excel or direct values.
- Close the parenthesis and press
**Enter**

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.

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

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

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

### Recommended Articles

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 –

## Leave a Reply