Excel function · Finding text
SEARCH
Finds where one text starts inside another, ignoring case.
What it does
SEARCH works like FIND, but "A" and "a" count as the same and you can use wildcards: ? for any one character and * for any run of characters. When the text isn't there, it returns #VALUE!.
Syntax
=SEARCH(find_text, within_text, [start_num])Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,
Arguments
- find_text
- The text to look for. It can contain ? and *.
- within_text
- The text to search in.
- start_num
- The position to start searching from. Leave it out to start at 1.
Example
Product names mark professional models with PRO. Where does it start in Model X-200 PRO?
| A | B | |
|---|---|---|
| 1 | Product | "pro" starts at |
| 2 | Model X-200 PRO | 13 |
The formula in B2:
=SEARCH("pro",A2)Result: 13
"pro" matches PRO because case is ignored. Counting the letters, spaces and hyphen, it starts at character 13.
Common mistakes
- Using
SEARCHto test whether a text is there without catching the error. Wrap it inISNUMBERto get TRUE or FALSE. - Forgetting that ? and * are wildcards. To look for a real question mark, write ~?.
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | SEARCH | , |
| Deutsch | SUCHEN | ; |
| Français | CHERCHE | ; |
| Español (España) | HALLAR | ; |
| Español (México) | HALLAR | , |
| 日本語 | SEARCH | , |
| 한국어 | SEARCH | , |
| 繁體中文 | SEARCH | , |
| ไทย | SEARCH | , |
| ខ្មែរ | SEARCH | , |
| Tiếng Việt | SEARCH | ; |