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)

**Table of Contents**hide

**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:

`=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.

- 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

**, both will return 3.**

`SEARCH(10,"2510",1)`

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

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:

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

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**

tells where there is a space (“ “) in the name within cell`SEARCH(" ",C4,1)`

**C4**.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(C4,SEARCH(" ",C4,1)-1)`

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

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.

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**

searches for the text “`SEARCH("Novel",C4,1)`

**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.converts it into`ISNUMBER(SEARCH("Novel",C4,1))`

**TRUE**if it had a number, and converts it into**FALSE**if it had an error.returns a`IF(ISNUMBER(SEARCH("Novel",C4,1)),"Yes","No")`

**“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)`

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.

`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).

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

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