What it does
SEARCH works like FIND and returns the same kind of result: the position where the text starts. There are two differences: it ignores case ("postage" and "POSTAGE" are the same) and it understands the ? (one character) and * (any number of characters) wildcards. To look for a real question mark or asterisk, put ~ in front of it.
The most common use is testing whether a cell contains a word. Text that is not found gives #VALUE!, so the formula is wrapped in ISNUMBER: =ISNUMBER(SEARCH("postage",B2)) returns TRUE or FALSE, and IF can turn that into a label.
Case-insensitive matching of some letters depends on the language: in Turkish, the capital of i is İ and the lower case of I is ı, so results can differ between computers with different language settings. Where case matters, as with codes, use FIND.
Syntax
=SEARCH(find_text, within_text, [start_num])=MBUL(bul_metin; metin; [başlangıç])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
find_text | Required | The text to look for. Case is ignored; ? stands for one character and * for any number of characters. |
within_text | Required | The text, or cell, to search in. |
start_num | Optional | The character position where the search starts; 1 if omitted. The result is still counted from the start of the text. |
Examples
1Label expenses whose description mentions postage
The descriptions were typed by different people in different cases. SEARCH ignores case, so it catches all of them.
=IF(ISNUMBER(SEARCH("postage",B2)),"Postage","Other")| A | B | C | |
|---|---|---|---|
| 1 | Expense | Description | Type |
| 2 | E-201 | Postage fee | Postage |
| 3 | E-202 | POSTAGE not included | Postage |
| 4 | E-203 | Assembly service | Other |
| 5 | E-204 | Express postage | Postage |
| 6 | E-205 | Spare parts | Other |
- C2 (filled down to C6)
=IF(ISNUMBER(SEARCH("postage",B2)),"Postage","Other")
E-202, written in capitals, is caught too: C3 shows Postage. The assembly and spare parts rows get Other.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Strip the unit from a weight and keep the number
The supplier's list writes the weight together with "kg", spelled in different ways. SEARCH finds the unit; the part before it is taken, trimmed and turned into a number with two minus signs.
=--TRIM(LEFT(B2,SEARCH("kg",B2)-1))| A | B | C | |
|---|---|---|---|
| 1 | Product | Weight | kg |
| 2 | Cement | 25 kg | 25 |
| 3 | Sand (bag) | 750 KG | 750 |
| 4 | Plaster | 12 Kg | 12 |
| 5 | Lime | 1250 kg | 1250 |
- C2 (filled down to C5)
=--TRIM(LEFT(B2,SEARCH("kg",B2)-1))
The "KG" and "Kg" spellings are found as well; column C now holds numbers you can add up: 25, 750, 12, 1250.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Find codes that match a pattern with wildcards
Codes from the old system have only two characters between the hyphens. The pattern "-??-" finds codes with exactly two characters between two hyphens.
=ISNUMBER(SEARCH("-??-",A2))| A | B | |
|---|---|---|
| 1 | Product Code | Old Code? |
| 2 | KBL-15-SY | TRUE |
| 3 | KBL-1525-SY | FALSE |
| 4 | PRZ-21-BY | TRUE |
| 5 | SGT-0016-BY | FALSE |
- B2 (filled down to B5)
=ISNUMBER(SEARCH("-??-",A2))
KBL-15-SY and PRZ-21-BY match the pattern (TRUE); the four-digit codes do not (FALSE).
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: The text was not found, or start_num is less than 1 or beyond the end of the text.
Fix: For "does it contain…?" tests, wrap the formula in ISNUMBER; when you need the position, use IFERROR to give rows without a match a value.
- Unexpected match
Cause: A short search text also appears inside other words: searching for "art" also finds "part" and "start".
Fix: Use a longer pattern, or search for the word with spaces around it:
=SEARCH(" art "," "&B2&" ").
Good to know
- Wildcards only work in SEARCH; in FIND,
?is just a question mark. - To count the rows that contain a word, you do not need SEARCH:
=COUNTIF(B2:B50,"*postage*")counts them directly.
In Nexell
Get Nexell to do it from a sentence
Write "Postage" in a Type column for rows whose description mentions postage, and "Other" for the rest
Convert to tableBring your expense list into the tool and type this. Nexell adds the Type column with real Excel formulas and the preview shows which row gets which label; in the downloaded file, the type follows the description.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I search for one of several words?
Write a SEARCH for each word and combine them with OR: =OR(ISNUMBER(SEARCH("postage",B2)),ISNUMBER(SEARCH("freight",B2))). It returns TRUE if any of the words appears.