How to Use SEARCH Function in Excel (3 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel SEARCH Function Overview


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.

Dataset of Filtering out of first name of excel search function

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)

Filtering out of first name of excel search function

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

Filtering out of first name of excel search function

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

Filtering out of first name of excel search function

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

Dataset of Filtering out a specific type of book of excel search function

  • Then double-click on the Fill 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 Fill 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:

=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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo