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