In Excel, we need to deal with a large number of formulas and functions. Moreover, the Excel **SEARCH** function is quite familiar function in use. This function is a handy function. Today I will be showing how you can use** the SEARCH function** of Excel to search for a particular text within a given string.

The following image gives an overview of how the **SEARCH** function of MS Excel works.

**Introduction to Excel SEARCH Function**

**Summary**

The **SEARCH** function returns the number of characters after finding a specific character or text string, reading from left to right. This function Searches for a case-insensitive match. It works for both **Array** and **Non-Array Formula **and is available from Excel 2003.

**Syntax**

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

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

**Arguments**

ARGUMENT | REQUIREMENT | EXPLANATION |
---|---|---|

find_text |
Required | The text that is searched for. Can be a single text or an array of texts. |

within_text |
Required | The text value within which the find_text argument is searched for. Can be a single text value or an array of text values. |

[start_num] |
Optional | The position of the within_text argument from which it starts searching. Can be a single number or an array of numbers. Default is 1. |

**Note:**

- All three arguments can be either a single value or an array of values.
- In the case that at least one of the arguments is an array, the formula will turn into an
**Array Formula**and you have to press**Ctrl + Shift + Enter**to enter the formula. - The third argument
**[start_num]**is optional. Default is**1**.

**Return Value**

Returns the number of the character at which the specific character or text string (find_text) is first found, reading from left to right.

**Available in**

Excel 2003 or higher versions.

**How to Use ****Excel SEARCH Function: 3 Suitable Examples**

Let’s try to see some examples of using the **SEARCH** function of Excel. hopefully, these examples will help you to improve your skill of excel. I hope you will get interested in the examples. Follow the rules of every procedure. Moreover, I have added illustrations for your better understanding.

**1. Filtering Out the First Names from Some Names**

Look at the data set below. We have the Employee IDs and Employee Names of some employees of a company named Mars Group. the dataset has two columns called **Employee ID** and **Employee Name**. The dataset ranges from **B4** to **C21** cells.

Now we will extract out the first names of the employees.

**Steps:**

- In the first cell of a new column, in D5, enter this formula:

`=LEFT(C4,SEARCH(" ",C4,1)-1)`

- Then, click the
**enter**button. - As a result, you will find the first name of the first employee. Then,
**Fill Handle**the formula from**D5**to**D21**cell.

- Consequently, you will find the result just like the picture given below.

See, we have extracted out all the first names.

**🔎** **How Does the Formula Work?**

**SEARCH(” “,C4,1):**Tells where there is a space (“ “) in the name within cell**C4**.**LEFT(C4,SEARCH(” “,C4,1)-1):**Extract the string up to just before the space from the left. This is the first name we are looking for. See**the LEFT function**for details.

**2. Filtering Out a Specific Type of Book from a List of Books**

Now, look at this new data set. We have the names of some books and their book types from a shop named Kingfisher Bookstore. The dataset has two columns called **Book Name** and **Book Type**. The dataset ranges from **B4** to **C21**.

Now, we will try to figure out for each book, whether that is a novel or not.

**Steps:**

- Enter this formula in the first cell of a new column:

`=IF(ISNUMBER(SEARCH("Novel",C4,1)),"Yes","No")`

- Then double-click on the
**F****ill Handle**to fill the rest of the cells.

- As a consequence, you will find the result just like the picture given below.

See, we have found out for each book whether it is a novel or not. “Yes” if it is, ‘No” if it is not it.

**🔎 How Does the Formula Work?**

**SEARCH(“Novel”,C4,1):**Searches for the text “**Novel”**in the book type of the book. If it finds a match, it returns a number, if it does not find a match, it returns**#VALUE!**Error.**ISNUMBER(SEARCH(“Novel”,C4,1)):**Converts it into**TRUE**if it had a number, and converts it into**FALSE**if it had an error.**IF(ISNUMBER(SEARCH(“Novel”,C4,1)),”Yes”,”No”):**Returns a**“Yes”**if it finds a**TRUE**, and returns a**“No”**if it finds a**FALSE**.- Thus a
**“Yes”**is returned if the**Book Type**contains the text**“Novel”**in it, and a**“No”**otherwise.

**3. ****Using Array Formula with SEARCH Function**

Up to now, we have applied the formula to one cell and then dragged the **F****ill Handle** **to copy the formula** to the rest of the cells. This time we shall apply the formula to all the cells simultaneously. Let’s try to extract the first names from all the names of Example 1 simultaneously.

**Steps:**

- Apply an
**Array Formula**. The formula will be:

`=LEFT(C4:C20,SEARCH(" ",C4:C20,1)-1)`

It returns the first names from all the names simultaneously.

**🔎 How Does the Formula Work?**

If we break the formula **LEFT(C4:C20,SEARCH(” “,C4:C20,1)-1)**, we will see that it consists of 17 single formulas.

**LEFT(C4,SEARCH(” “,C4,1)-1)****LEFT(C5,SEARCH(” “,C5,1)-1)****LEFT(C6,SEARCH(” “,C6,1)-1)**

**…**

**…**

**…**

**LEFT(C20,SEARCH(” “,C20,1)-1)**

Each of them extracts the first name from the full names (See Example 1). the formula first searches for a space(“ ”) in each cell from the range C4 to C20. Then extracts the strings up to just before the space for all the cells, starting from left. Thus extracts out the first names from all the cells simultaneously.

Now if you want, you can decide for each book in the booklist of example 2 as well, using the **Array Formula. **Follow the following steps. I have added the necessary screenshot with the steps for your better understanding.

**Steps:**

- Copy the following formula.

`=IF(ISNUMBER(SEARCH("Novel",C4:C20,1)),"Yes","No")`

- After pressing enter, you will find the following result.

**Download Practice Workbook**

**Conclusion**

In this article, I have tried to explain how to use SEARCH function in Excel. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website Exceldemy.com. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.

**<< Go Back to Excel Functions ****|**** Learn Excel**