How to Use SEARCH Function in Excel (3 Examples)

Today I will be showing how you can use the SEARCH function of Excel to search for a particular text within a given string.

SEARCH Function of Excel (Quick View)

Download Practice Workbook

SEARCH Function of Excel: Syntax and Argument

Summary

• Returns the number of the character at which a specific character or text string is first found, reading from left to right.
• Searches for a case-insensitive match.
• Works for both Array and Non-Array Formula.
• Available from Excel 2003.

Syntax

The Syntax of the SEARCH function is:

Argument

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.

Special Notes

• The SEARCH function can also search for a number within another number.

For example, the formula SEARCH(10,2510,1) returns 3 because it finds a 10 from the 3rd position of 2510.

• It can also search for a number wrapped as a text within another number, or a number within a number wrapped as text.

For example, the formula SEARCH("10",2510,1) and the formula SEARCH(10,"2510",1), both will return 3.

• In case of texts, the SEARCH function looks for a case-insensitive match.

For example, the formula SEARCH("BIOGRAPHY","Autobiography",1) will return 5.

• In case the SEARCH function does not find any match, it will return #VALUE! Error.

For example, the formula SEARCH("Book","Autobiography",1) will return a #VALUE! Error, because the text “Book” is not present within the text “Autobiography”.

Excel SEARCH Function: 3 Examples

Let’s try to see some examples of using the SEARCH function of Excel.

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.

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

Quite simple. In the first cell of a new column, enter this formula:

Then double click on the Fill Handle to fill the rest of the cells.

See, we have extracted out all the first names.

Explanation of the Formula

• SEARCH(" ",C4,1) tells where there is a space (“ “) in the name within cell C4.
• LEFT(C4,SEARCH(" ",C4,1)-1) then extracts 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.

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

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

Then double click on the Fill Handle to fill the rest of the cells.

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.

Explanation of the Formula

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

We have to apply an Array Formula.

The formula will be:

It returns the first names from all the names simultaneously.

Explanation of the Formula

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

1. LEFT(C4,SEARCH(" ",C4,1)-1)
2. LEFT(C5,SEARCH(" ",C5,1)-1)
3. LEFT(C6,SEARCH(" ",C6,1)-1)

…

…

…

17. LEFT(C20,SEARCH(" ",C20,1)-1)

Each of them extracts the first name from the full names (See Example 1).

Now if you want, you can decide for each book in the booklist of example 2 as well, using the Array Formula:

Common Errors with SEARCH Function

Conclusion

Using these methods, you can use the SEARCH function of Excel to search for a specific text within a string. Do you have any questions? Feel free to ask us.