In **Microsoft Excel**, the** FIND **function and **SEARCH **function enable finding the necessary alphabetic or numeric conjunction in a string, a cell with text data. In this article, we will see an overview of the **FIND** and **SEARCH** functions in Excel.

**Download Practice Workbook**

You can download the workbook and practice with them.

**Introduction to the FIND Function in Excel**

In Excel, the **FIND function** is a built-in function that is classified as a **String/Text Function**. The **FIND function** is accustomed to finding the location of a certain character or substring inside a text string.

**➧**** Syntax:**

The syntax for the **FIND function **is:

**FIND( find_text, within_text, [start_num])**

**➧**** Arguments:**

** find_text: **[required] The text we are looking for.

** within_text: **[required] The text includes the text we are looking for.

** start_num: **[optional] Defines the position where the search should begin. Character 1 is the first character within the text. If the start num is not specified, it is considered to be 1.

**➧**** Return Value:**

The find text location is represented by a number.

**Introduction to the SEARCH Function in Excel**

The **SEARCH function** allows searching for objects in the database using simple or complex search. This function gives the position of one text string inside another. It is categorized under Excel **String/Text Function**.

**➧**** Syntax:**

The syntax for the **SEARCH function **is:

**SEARCH( find_text,within_text,[start_num])**

**➧**** Arguments:**

** find_text: **[required] The text that searches.

** within_text: **[required] The text to search inside.

** start_num: **[optional] Position to begin searching in the text. The default value is 1.

**➧**** Return Value:**

The position of the find text is represented by a number.

**Excel FIND Function VS Excel SEARCH Function**

In Excel, the **FIND** function and **SEARCH **function are quite similar and do the same goal, but in slightly different yet crucial ways. The primary distinction between Excel **FIND **and** SEARCH** functions is that **SEARCH** is case-insensitive, whereas **FIND** is case-sensitive. Another difference is that **SEARCH** allows for the use of wildcards whereas** FIND** does not.

**5 Examples with the Comparative Outputs from the FIND and SEARCH Functions in Excel**

The** FIND** and** SEARCH** capabilities in Excel are rarely utilized independently. They are typically used in conjunction with other functions such as **MID**, **LEN**, **LEFT**, or **RIGHT**, and **IFERROR** we will see those in the examples below.

**1. Insert Excel FIND & SEARCH Function in a Text Cell**

We can insert **FIND **and **SEARCH **functions to detect the position of that specific word or letter. Suppose, we want to find ‘**e**’ in **Excel**, so we put the text string in column **B, **and the result of the position for the text string is in column **C**. Follow the below steps:

- So, firstly, we are going to use the
**FIND****function**to find the position of ‘**e**’. For this, we need to select cell**C5**. After that, put the formula into that cell.

`=FIND("e",B5)`

- Then, pressing
**Enter**on the keyboard will show the result which is**4**. The formula returns**4**because**e**is the**4**letter in the word^{th }**Excel**. - Now, secondly, we are going to use the
**SEARCH function**to search the position of ‘**e**’. To do this, we have to choose the cell where we want to see the result, so we choose cell**C6**. Now, in that particular cell, enter the formula.

`=SEARCH("e",B6)`

- And, press
**Enter**. And, we can see that the result is**1**. The formula returns**1**because**E**is the first character in word**Excel**. As we know the**SEARCH function**is not case sensitive like the**FIND function**, so it doesn’t care if the letter is capital or small. When it finds the letter it will immediately show the result.

- So, by using the
**FIND**and**SEARCH**functions we can discover the position of any word or letter in a text string.

**Read More:** **How to Find Text in Cell in Excel**

**2. Apply FIND Function to Find a String That Comes before or after a Given Character**

Suppose, we have some names in column **B** and we want to find the first and last name of each name respectively in columns **C**, and **D**. To do this, we need to follow the below procedure:

- First, to get the first name, we select cell
**C5**. We can use the**FIND**or**SEARCH**function in combination with the**LEFT function**. So, we are using the**FIND**function. - Second, put the formula in cell
**C5**.

`=LEFT(B5, FIND(" ", B5)-1)`

- Then, press
**Enter**.

- Now, we want to get the last name, for this, we can use the conjunction of the
**RIGHT**,**FIND**or**SEARCH**and**LEN**functions. So, by the same token as before, select cell**D5**and put the formula in there.

`=RIGHT(B5,LEN(B5)-FIND(" ",B5))`

- After pressing the
**Enter**key, you will get the last name in the resulting cell.

Here, the **LEN function **will accrue the total number of characters, and then the **FIND function** will subtract the position of the space. And finally, the **RIGHT function** will show the right-sided characters.

- Next, to copy the formula over column
**C**, drag the**Fill Handle**down or just**double click**on the**plus**(‘**+**’) sign.

- Similarly, drag the
**Fill Handle**over column**D**to duplicate the formula.

- Finally, this will extract all the first names and last names from the names.

**Note: **We use the **FIND **function instead of **SEARCH **because we want to get a more accurate value, As we all know that the **FIND function **is case sensitive.

**Read More:** **How to Find a Character in String in Excel**

**3. Insert FIND Function to Find the Nth occurrence of a Particular Character in Excel**

Assume that, we have some text string with a special character **dash** (‘**–**’) in column **B**. And we want to find the **2**** ^{nd}** and

**3**

**positions of the**

^{rd}**dash**(‘

**–**’) consecutively in columns

**C**, and

**D**. To get the position of the special character, let’s follow the steps down.

- Likewise the earlier examples, first, select the cell in your spreadsheet where you want to see the result after using the formula. So, we select cell
**C5**. - Second, put the formula into that cell.

`=FIND("-", B5, FIND("-",B5)+1)`

- Third, press
**Enter**to see the result.

Here, the formula is to get the **2**^{nd}^{ }position.

- Further, drag the
**Fill Handle**down to copy the formula.

- Next, we want to get the
**3**position in column^{rd}**D**. So, we select cell**D5**and put the formula there.

`=FIND("-",B5, FIND("-", B5, FIND("-",B5)+1) +2)`

- Furthermore, press the
**Enter**key on the keyboard.

Here, the formula will show the **3**** ^{rd}** position of the special character.

- And, now, again drag the
**Fill Handle**down to duplicate the formula over column**D**. - Finally, by following the steps, you will get your desired result.

**Read More:** **How to Find Character in String Excel (8 Easy Ways)**

**Similar Readings**

**Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)****How to Find Lowest 3 Values in Excel (5 Easy Methods)****Find First Occurrence of a Value in a Range in Excel (3 Ways)****Find External Links in Excel (6 Quick Methods)****How to Find If Cell Contains Specific Text in Excel**

**4. Apply SEARCH Function to Find Text between Parentheses**

Suppose, we have some text string, and we want to get only the text which is enclosed by brackets. To get those text strings in the **Parenthesis** we need the **MID function** to separate the necessary number of characters from a string. As well as the **FIND** or **SEARCH** functions to figure out what to begin and how many letters to separate. Let’s follow the steps below.

- In the beginning, select the cell, where you want to see the result. So, we select cell
**C5**. - Then, copy and paste the formula into that cell.

`=MID(B5,SEARCH("(",B5)+1, SEARCH(")",B5)-SEARCH("(",B5)-1)`

- Next, press
**Enter**.

All you need to do is change the cell number as per your cell location, after pasting the formula.

- After that, drag the
**Fill Handle**down to duplicate the formula.

- And, finally, the text inside the parenthesis is now extracted from the whole text string.

🔎 **How Does the Formula Work?**

⏩ **SEARCH(“(“, B5)+1: **This will take the cell value from cell **B5**, and search for the position of the opening bracket ‘**(**’ which is **4+1**, from **SEARCH(“(“, B5) **we will get **4**, cause the space is also counted.

**Output → 5**, which is the first letter inside the parenthesis ‘**0**’.

⏩ **SEARCH(“)”, B5)-SEARCH(“(“, B5)-1: **This will find the position of the closing bracket ‘**)**’. And, subtract the opening bracket position.

**Output → 10-4-1; ****⇒**** 6-1; ****⇒**** 5**, which is the last letter inside the parenthesis ‘**1**’.

⏩ **MID(B5,SEARCH(“(“,B5)+1, SEARCH(“)”,B5)-SEARCH(“(“,B5)-1): **This will extract the text inside the bracket.

**Output → 01001**

**Read More: Excel Search for Text in Range (11 Quick Methods)**

**5. Combine FIND & IFERROR Functions to Handle the Error to Find Text**

If the ** find_text **is not found then it shows an error message in both

**FIND**and

**SEARCH**functions. Rather than showing the error message we may use the

**IFERROR**function for displaying an expressive message like ‘

**Not Found**’. For example, in cell

**C10**, a user can input any text and cell

**C5**will be searched for the entered text.

- First, select the cell where you want the position of the imputed text in cell
**C10**. So, we select cell**C11.** - Second, put the formula in cell
**C11b**.

`=IFERROR(FIND(C10, C5), "Given text is not found!")`

- Then, press the
**Enter**key on your keyboard. As you can see, it will show**1**. Because the text**Excel**is found only one time in cell**C5**.

- The text
**Indesign**is not found in cell**C5**. So, it will give a message ‘**Given text is not found!**’.

**Read More:** **How to Use Formula to Find Bold Text in Excel**

**Things to Keep in Mind**

If any of the following conditions are met, the Excel** FIND **and **SEARCH** function produces the** #VALUE!** error:

- Within text does not have the function
.*find_text* has fewer characters than*within_text*.*start_num*is either zero (‘*start_num***0**’) or a negative value oris less than or equal to zero (‘*start_num***0**’).

**Conclusion**

The above will give you an overview of the **FIND** and **SEARCH **functions in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the **ExcelDemy.com** blog!

**Related Articles**

**How to Use Excel Formula to Find Last Row Number with Data (2 Ways)****How to Find First Value Greater Than in Excel (4 Ways)****[Solved!] CTRL+F Not Working in Excel (5 Fixes)****How to Find * Character Not as Wildcard in Excel (2 Methods)****[Fixed]: Can’t Find Project or Library Error in Excel (3 Solutions)****How to Find Last Non Blank Cell in Row in Excel (5 Methods)**