Skip to content

Excel function · Text

Excel SEARCH function

SEARCH tells you at which character one text starts inside another, ignoring case and accepting wildcards. It is the usual answer to "does this description mention postage?"

In Turkish Excel: MBUL

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

English Excel=SEARCH(find_text, within_text, [start_num])
Turkish Excel=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

ArgumentRequiredDescription
find_textRequiredThe text to look for. Case is ignored; ? stands for one character and * for any number of characters.
within_textRequiredThe text, or cell, to search in.
start_numOptionalThe 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.

C2fx=IF(ISNUMBER(SEARCH("postage",B2)),"Postage","Other")
Label expenses whose description mentions postage: example table
ABC
1ExpenseDescriptionType
2E-201Postage feePostage
3E-202POSTAGE not includedPostage
4E-203Assembly serviceOther
5E-204Express postagePostage
6E-205Spare partsOther
  • 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.

C2fx=--TRIM(LEFT(B2,SEARCH("kg",B2)-1))
Strip the unit from a weight and keep the number: example table
ABC
1ProductWeightkg
2Cement25 kg25
3Sand (bag)750 KG750
4Plaster12 Kg12
5Lime1250 kg1250
  • 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.

B2fx=ISNUMBER(SEARCH("-??-",A2))
Find codes that match a pattern with wildcards: example table
AB
1Product CodeOld Code?
2KBL-15-SYTRUE
3KBL-1525-SYFALSE
4PRZ-21-BYTRUE
5SGT-0016-BYFALSE
  • 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

Type this in Nexell

Write "Postage" in a Type column for rows whose description mentions postage, and "Other" for the rest

Convert to table

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

How do I return text if a cell contains a word?

Combine SEARCH with ISNUMBER and IF: =IF(ISNUMBER(SEARCH("postage",B2)),"Postage","Other"). SEARCH ignores case, so "Postage" and "POSTAGE" are caught as well.

Related functions

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free