Text
SEARCH
Definition
SEARCH returns the starting position of one text inside another, ignoring case.
Syntax
=SEARCH(find_text, within_text, [start_num])
Arguments
| Argument | Meaning |
|---|---|
find_text |
The characters to look for. Uppercase and lowercase both match. |
within_text |
The text, or the cell holding it, to search. |
start_num |
Optional. The character at which to begin looking. If left out, Excel starts at 1. |
Worked example
A travel list stores city names in mixed case. The names are in A2:A4.
| A: City |
|---|
| Boston |
| Lisbon |
| Canton |
Formula: =SEARCH("TON",A2)
Result: 4. TON matches ton in Boston, so SEARCH returns character 4 even though the case differs.
Practice
Cell A2 holds Packet. In B2, write a formula that returns the position of CK inside A2, ignoring case.
- In B2, write a SEARCH formula that returns the position of CK in A2.
- B2 should show the position of CK in A2.
Step 1 of 2
Enter =SEARCH("CK",A2) in B2.
CK matches ck in Packet, so the position is 3.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses SEARCH.
B2 now shows 3.
Related functions
SEARCH is the case-insensitive partner of FIND, and MID can then take characters from the position SEARCH returns.
Common mistake
Forgetting that ? and * are wildcards in SEARCH, so a search for A*B matches more than the three characters A, *, and B.