Where does “@” appear, or the word “error”? SEARCH returns the character position of text within a cell — the building block for splitting, extracting, and testing.
The example
The @ sits at position 4.
| A | B | |
|---|---|---|
| 1 | Text | Position of @ |
| 2 | ann@x.com | 4 |
The formula
The character index of the match:
How it works
SEARCH locates text and returns its position:
SEARCH(find, within)returns the starting position of find inside within.- It’s case-insensitive and allows wildcards;
FINDis case-sensitive and literal. - A 3rd argument sets the start position — search from there to find a later occurrence.
- Use the position with LEFT/MID/RIGHT to split or extract around it; wrap with
ISNUMBERto test “does it contain?”
“Contains” test: =ISNUMBER(SEARCH("error", A2)) returns TRUE/FALSE — SEARCH gives a number when found and an error when not, and ISNUMBER turns that into a clean yes/no.
Try it: interactive demo
Find a substring’s position.
Variations
Case-sensitive
Use FIND:
Contains?
Yes/no:
Nth occurrence
Start past the first:
Pitfalls & errors
Not found = error. SEARCH returns #VALUE! when the text isn’t there. Wrap with IFERROR, or use ISNUMBER for a test.
SEARCH vs FIND. SEARCH ignores case and allows wildcards; FIND is exact-case. Pick deliberately.
Position is 1-based. The first character is position 1, not 0.
Practice workbook
Frequently asked questions
How do I find the position of text in a cell in Excel?
How do I test whether a cell contains some text?
How do I find a later occurrence?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class