Text
How to Use FIND and SEARCH in Excel (Find Text Position)
FIND and SEARCH both return the position of one piece of text inside another, counting from the left. You use that number to know where a separator sits, then feed it to LEFT, RIGHT, or MID to split the text.
The two functions are almost identical, with two key differences: FIND is case-sensitive and SEARCH is not, and SEARCH accepts wildcards while FIND does not. This guide covers the syntax, examples, and when to pick each one.
FIND and SEARCH syntax
=FIND(find_text, within_text, [start_num]) =SEARCH(find_text, within_text, [start_num]) | Argument | Required | What it does |
|---|---|---|
find_text | Required | The text you are looking for. FIND matches case exactly; SEARCH ignores case and allows the ? and * wildcards. |
within_text | Required | The text or cell to search inside. |
start_num | Optional | The character position to begin searching from. Optional; defaults to 1. |
Note: Both return the position as a number, or #VALUE! if the text is not found. Use FIND for case-sensitive matches and SEARCH for case-insensitive or wildcard matches. Works in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Find the position of a character
=FIND(" ", A2) Returns the number of the character where the space sits. That position is the key to splitting first and last names with LEFT or MID.
2. Search without worrying about case
=SEARCH("st", A2) SEARCH ignores case, so it matches "st", "St", or "ST". FIND would only match the exact casing you typed.
3. Split text at a separator
=LEFT(A2, FIND("@", A2)-1) FIND locates the @, and LEFT takes everything before it. The -1 stops just before the @ so it is not included.
4. Test whether a cell contains a word
=ISNUMBER(SEARCH("paid", A2)) SEARCH returns a number when it finds the text and an error when it does not; ISNUMBER turns that into a clean TRUE or FALSE.
How to write FIND and SEARCH step by step
- 1
Choose FIND for a case-sensitive match or SEARCH for case-insensitive (or wildcards).
- 2
Type =FIND( (or =SEARCH() and the text to find in quotes, then a comma.
- 3
Click the cell to search inside, then close the bracket ) and press Enter.
- 4
To split text, nest it inside LEFT, RIGHT, or MID, e.g. =LEFT(A2, FIND(" ", A2)-1).
- 5
Wrap it in IFERROR or ISNUMBER if the text might be missing.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#VALUE! | The text you searched for is not in the cell, or start_num is past the end of the text. | Confirm the text exists (mind the case for FIND), or wrap the function in IFERROR or ISNUMBER. |
FIND misses a match | FIND is case-sensitive, so "St" does not match "st". | Use SEARCH for a case-insensitive match, or fix the casing with UPPER or LOWER first. |
Wildcards are ignored | You used * or ? with FIND, which treats them literally. | Use SEARCH, which supports the ? and * wildcards. |
Off-by-one in a split | The separator position was included in the extracted text. | Subtract 1 for LEFT and add 1 for MID, e.g. FIND(" ", A2)-1. |
Frequently asked questions
What is the difference between FIND and SEARCH in Excel?
FIND is case-sensitive and does not allow wildcards. SEARCH is case-insensitive and supports the ? and * wildcards. Otherwise their arguments and results are the same.
Which is faster, FIND or SEARCH?
The speed difference is negligible. Choose based on behavior: FIND when case matters, SEARCH when it does not or when you need wildcards.
How do I check if a cell contains specific text?
Wrap SEARCH in ISNUMBER: =ISNUMBER(SEARCH("paid", A2)) returns TRUE if the cell contains "paid" in any case, and FALSE otherwise.
Why does FIND return #VALUE!?
It means the text was not found. Check the spelling and capitalization, since FIND is case-sensitive, or switch to SEARCH and wrap it in IFERROR for a safe fallback.