Function guides · Text
SEARCH function
Returns the position of one text string inside another, ignoring case and allowing wildcards.
Syntax
=SEARCH(find_text, within_text, [start_num])
| Argument | What it is |
|---|---|
find_text | The text to look for. Not case sensitive. ? and * work as wildcards. |
within_text | The text to search in. |
start_num | Optional. The position to start from. Defaults to 1. |
What SEARCH does
SEARCH finds where one piece of text begins inside another and returns the position, counting from 1. It is the forgiving version of FIND: capital and lowercase letters are treated alike, and the wildcards ? and * work.
That makes it the better choice for matching typed text, where nobody can be trusted to capitalize consistently.
The most useful pattern wraps it in ISNUMBER. SEARCH returns a position if the text is there and an error if not, so ISNUMBER converts the result into TRUE or FALSE for use in IF. Everything else about it, including the optional start position, matches FIND.
If FIND and SEARCH both seem to work, choose by data. Typed free text, such as comments, descriptions and notes, is usually inconsistent in capitalization, so SEARCH is the safe default. Codes and IDs with meaningful case, such as a lowercase suffix that means a different product line, call for FIND.
Wildcards extend what SEARCH can do. “a*z” finds text that starts with an a and later has a z, and “??-” finds a dash after two characters. The wildcard rules match those of COUNTIF and SUMIF, so one set of habits covers all three.
SEARCH examples
1. Case does not matter
| A | B | |
|---|---|---|
| 1 | Product | |
| 2 | Kona PRO Kit | 6 |
Searching for "pro" in "Kona PRO Kit" finds PRO at position 6. FIND would return #VALUE! for the same search.
2. A contains test inside IF
| A | B | |
|---|---|---|
| 1 | Note | |
| 2 | Customer asked for a REFUND | Refund |
ISNUMBER turns the position into TRUE and the error into FALSE, which makes a clean yes-or-no. Any description mentioning "refund", in any case, is flagged.
3. Wildcards in the search text
| A | B | |
|---|---|---|
| 1 | Item | |
| 2 | Red Bag | 5 |
A question mark stands for any single character and an asterisk for any run. "B?g" matches "Bag", which starts at position 5 of "Red Bag". To search for a real question mark or asterisk, put a tilde in front of it.
Common SEARCH mistakes
- Expecting 0 when not found. SEARCH returns #VALUE!. Use ISNUMBER or IFERROR around it.
- Wildcard characters in the data. If you are searching for a literal question mark or asterisk, escape it with a tilde, as in "~?". Otherwise it matches anything.
- Using SEARCH where case matters. To tell "US" from "us", use FIND, which is case sensitive.
- Forgetting the start_num on repeats. SEARCH finds the first match. Start after it to find the next.
SEARCH on Excel job tests
"Flag any row whose description mentions refund", in any capitalization, is the SEARCH job. Learn the pattern ISNUMBER(SEARCH("refund",A2)) cold. It works inside IF, in conditional formatting rules and inside SUMPRODUCT.
If the task only needs a count, COUNTIF(A2:A50,"*refund*") is simpler. SEARCH becomes necessary when the position feeds another function such as MID.
Try REFUND, Refund and refund request on your own copy of the data to confirm the result does not depend on case.
Related functions
SEARCH questions
- What is the difference between SEARCH and FIND?
- SEARCH ignores case and accepts wildcards. FIND is case sensitive and does not.
- How do I test whether a cell contains a word?
=ISNUMBER(SEARCH("word",A2))returns TRUE or FALSE.- What does SEARCH return when nothing matches?
- #VALUE!.
- How do I search for a question mark itself?
- Type a tilde before it: SEARCH("~?",A2).
Practice SEARCH
The lesson FIND and SEARCH: locate text (module 9 of Everyday Excel) has checked SEARCH exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample