How to Use SEARCH Function in Excel (3 Examples)

Quick View of the SEARCH 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.

SEARCH Function of Excel (Quick View)

Quick View of the SEARCH Function

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

Syntax of the SEARCH Function

The Syntax of the SEARCH function is:

=SEARCH(find_text,within_text,[start_num])

Argument

Argument Required or Optional Value
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.

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.

SEARCH Function with Numbers

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

SEARCH Function with Numbers Wrapped as Texts

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

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

SEARCH Function for Case-Insensitive Match

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

SEARCH Function with No Match

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.

Data Set of Employees in Excel

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

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

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

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

SEARCH Function to Extract First Names from Some Names

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.
Formula Output Explanation
=LEFT(C4,SEARCH(” “,C4,1)-1) Morris Extracts the string from the left up to just before where there is a space in cell C4. That is the required first name.

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.

Data Set of Books in Excel

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:

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

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

SEARCH Function to Find Novel or Not

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.
Formula Output Explanation
=IF(ISNUMBER(SEARCH(“Novel”,C4,1)),”Yes”,”No”) Yes It first searches whether there is the text “Novel” in book type. If there is, it returns a number. If there is not, it returns an error. Then converts the numbers into TRUE and the errors into FALSE. Finally returns a “Yes’ for the TRUE, and “No’ for a FALSE.

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:

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

SEARCH Array Formula

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)

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

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

Formula Output Explanation
=LEFT(C4:C20,SEARCH(” “,C4:C20,1)-1) Morris

Richard

Peter

Frank

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:

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

Array Formula with SEARCH

Common Errors with SEARCH Function

Error When They Show
#VALUE! Shows when it does not find any match within the string, or any argument in the function is of the wrong data type.

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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo