Rifat Hassan
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.
Download Practice Workbook
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.
3 Suitable Examples to Use Excel SEARCH Function
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.
Read More: How to use LEFT function in Excel (4 Examples)
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 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.
Similar Readings
- How to Use CONCATENATE Function in Excel (4 Examples)
- Use LEN Function in Excel (Formula and VBA Code)
- How to Use FIND Function in Excel (7 Suitable Examples)
- Use REPLACE Function in Excel (3 Suitable Examples)
- How to Use CLEAN Function in Excel (10 Examples)
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:
- 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.
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.
