Excel’s SEARCH function finds where one piece of text appears inside another and returns its position as a number. On its own that sounds modest, but combined with other functions it lets you check whether a cell contains a word, pull out part of a string, flag rows by keyword, or filter a list.
This guide explains the syntax, walks through examples, compares SEARCH with FIND, and covers the errors you’re likely to see. If you just want to look for something on your sheet, use the Find tool (Ctrl + F) instead; searching for something in Excel explains it.
SEARCH Syntax
=SEARCH(find_text, within_text, [start_num])
- find_text is the text you’re looking for, such as
"apple". - within_text is the cell or text to look in.
- start_num (optional) is the character position to start from. It defaults to 1.
SEARCH isn’t case-sensitive and supports wildcards. If it can’t find the text, it returns a #VALUE! error. The full details are in the reference for the SEARCH function.
How to Use the SEARCH Function
Step 1: Click a cell for the result
Say A2 contains Order-1042-Blue. Click B2.
Step 2: Enter the formula
Type =SEARCH("-",A2) and press Enter. The result is 6, because the first hyphen is the sixth character.
Step 3: Fill it down
Drag the fill handle to apply the formula to the rest of the column. Each row returns the position of the first hyphen in its own cell.
Practical SEARCH Examples
| Goal | Formula | Result for Order-1042-Blue |
|---|---|---|
| Does the cell contain “blue”? | =ISNUMBER(SEARCH("blue",A2)) | TRUE |
| Label rows by keyword | =IF(ISNUMBER(SEARCH("blue",A2)),"Blue item","Other") | Blue item |
| Text before the first hyphen | =LEFT(A2,SEARCH("-",A2)-1) | Order |
| Text after the first hyphen | =MID(A2,SEARCH("-",A2)+1,LEN(A2)) | 1042-Blue |
| Position of the second hyphen | =SEARCH("-",A2,SEARCH("-",A2)+1) | 11 |
| Wildcard match | =SEARCH("10??",A2) | 7 |
The ISNUMBER pattern is the most common use: SEARCH returns a number when the text is found and an error when it isn’t, and ISNUMBER turns that into a clean TRUE or FALSE.
Use SEARCH to Highlight or Filter Rows
- Highlight matches: select your data, go to Home > Conditional Formatting > New Rule > Use a formula, and enter
=ISNUMBER(SEARCH("urgent",$A2)). Every row containing “urgent” gets the formatting you choose. - Filter a list (Microsoft 365):
=FILTER(A2:C100,ISNUMBER(SEARCH(E1,A2:A100)))returns only the rows whose column A contains the word typed in E1. It works like a simple search box. - Count matches: for counting,
=COUNTIF(A2:A100,"*blue*")is simpler than SEARCH.
SEARCH vs. FIND
FIND works the same way but has two differences: it’s case-sensitive, and it doesn’t accept wildcards. Use SEARCH when you want “Blue” and “blue” to match. Use FIND when case matters, such as product codes where “Ab” and “AB” are different.
Troubleshooting SEARCH
- #VALUE! error. The text wasn’t found, or start_num is larger than the text length. Wrap the formula in IFERROR, for example
=IFERROR(SEARCH("x",A2),0), or use the ISNUMBER pattern. - Wrong position returned. Leading spaces count as characters. Clean the data with TRIM first.
- Wildcards match too much. An asterisk matches any number of characters. To search for an actual * or ?, put a tilde in front:
"~*". - A number isn’t found. SEARCH treats numbers as text, so it works on formatted numbers only as they’re stored, not as displayed. Search the underlying value or use TEXT first.
- The formula returns 1 unexpectedly. find_text is empty, so SEARCH matches at the first character.
Tips
- Put the search term in a cell and reference it, so you can change it without editing formulas.
- Combine SEARCH with SUBSTITUTE or REPLACE to edit text at the position it finds.
- In Microsoft 365, the newer TEXTBEFORE and TEXTAFTER functions can replace many LEFT/MID and SEARCH combinations.
- For replacing text across a sheet, use find and replace in Excel instead.
Frequently Asked Questions
Is the SEARCH function case-sensitive?
No. Use FIND for a case-sensitive search.
Can SEARCH look for partial matches?
Yes. It finds the text anywhere inside the cell, and you can use * and ? wildcards for patterns.
What does SEARCH return if the text isn’t found?
A #VALUE! error. Use ISNUMBER or IFERROR to handle it.
Can I search across several cells at once?
Yes, in Microsoft 365. SEARCH spills results when you give it a range, and works inside FILTER, SUM and other array formulas.
Does SEARCH work in Google Sheets?
Yes, with the same syntax and behavior.
Wrapping Up
Use =SEARCH(find_text, within_text) to find where text appears in a cell, wrap it in ISNUMBER to test whether a cell contains something, and combine it with LEFT or MID to extract parts of a string. Switch to FIND when case matters, and to Ctrl + F when you just need to locate something on the sheet.
Related: use REGEXEXTRACT in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.