SEARCH function
Quick answer
SEARCH returns the position of one piece of text inside another, ignoring upper and lower case, and it understands the ? and * wildcards.
- Syntax
=SEARCH(find_text, within_text, [start_num])- Most common use
=SEARCH("mug",B2)returns 9 (Finds "MUG" even though the case is different)
Most people use it inside ISNUMBER to answer a simple question: does this cell contain that word?
- Available in
- Every current version
- Category
- Text functions
- Returns
- A number: the position of the match, or #VALUE! if it isn't found.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=SEARCH(find_text, within_text, [start_num])
| Argument | Required? | What it is |
|---|---|---|
find_text | Required | The text to look for. ? matches any one character and * any run of characters; put ~ in front to find the symbol itself. |
within_text | Required | The text to search in. |
start_num | Optional | Position to start searching from. Leave it out to start at 1. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | =SEARCH("mug",B2) | 9 | Finds "MUG" even though the case is different |
| C3 | =ISNUMBER(SEARCH("cold",B3)) | TRUE | TRUE because the description mentions cold |
| C4 | =SEARCH("m?g",A4) | 8 | A wildcard: ? stands for any single character |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Product | Description | Result |
| Free shipping mug | Ceramic MUG, 12 oz | 9 |
| Steel water bottle | Keeps drinks cold | TRUE |
| Travel Mug Lid | Spare lid for travel mugs | 8 |
Practice SEARCH
Graded instantly, then re-checked on changed data.
Flag reviews about delivery
B2, return TRUE if the review in A2 mentions "delivery" in any case, and FALSE if not. Fill down to B6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #VALUE! when the text is missing | SEARCH returns an error, not 0. Use =ISNUMBER(SEARCH("x",A2)) for TRUE or FALSE, or IFERROR(SEARCH("x",A2),0). |
| It matches part of a word | SEARCH("cat",A2) also finds "category". Search for " cat " with spaces, or check the characters around the match, when you need whole words. |
| Searching for * or ? | They act as wildcards. Put a tilde in front to find the character itself: SEARCH("~?",A2). |
Tips
- To count cells containing a word, =COUNTIF(A2:A100,"*delivery*") is shorter and also ignores case.
Questions
How do I check if a cell contains specific text?
=ISNUMBER(SEARCH("text",A2)) returns TRUE if the text appears anywhere in the cell, in any case. Use FIND instead of SEARCH for a case-sensitive check.
Does SEARCH support wildcards?
Yes. ? matches any single character and * matches any sequence of characters. FIND doesn't support them.
Answers that use SEARCH
In other languages
SEARCH is HALLAR in Spanish Excel and SUCHEN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .