Function guides · Text
FIND function
Returns the position of one text string inside another, matching case exactly.
Syntax
=FIND(find_text, within_text, [start_num])
| Argument | What it is |
|---|---|
find_text | The text to look for. Case sensitive, and wildcards are not allowed. |
within_text | The text to search in. |
start_num | Optional. The position to start searching from. Defaults to 1. |
What FIND does
FIND tells you where a piece of text starts inside a larger piece, counting from 1. It returns a number, not the text.
On its own that number is rarely the goal. It feeds LEFT, MID and RIGHT, which need to know where to cut. A name splits at the space, an email at the at sign, a code at the dash.
FIND is strict. It distinguishes capital and lowercase letters, and it treats wildcard characters as ordinary text. SEARCH is its relaxed twin, covered on its own page.
When nothing is found you get #VALUE!, which is useful if you wrap it in ISNUMBER to ask “does this contain that”.
A common pattern is the contains test. =ISNUMBER(FIND("West",A2)) returns TRUE if the cell has the word and FALSE if it does not, because FIND returns a number when it succeeds and an error when it does not. Wrap the test in IF to turn it into labels, or in SUMPRODUCT to count matching rows across a range.
Remember that FIND counts characters, including spaces, from 1. When the answer looks one off, count on your fingers across the text, then check whether the formula needs a plus or minus 1 after it.
FIND examples
1. Position of a space
| A | B | |
|---|---|---|
| 1 | Full name | Space at |
| 2 | Mei Tanaka | 4 |
FIND counts from 1. In "Mei Tanaka" the space is the fourth character, so FIND returns 4. This number then feeds LEFT, MID or RIGHT to split the name.
2. Case matters
| A | B | |
|---|---|---|
| 1 | Product | |
| 2 | Kayak Kit | 5 |
FIND treats "k" and "K" as different. The product name starts with a capital K at position 1, so searching for the lowercase letter skips it and lands on the k at position 5.
3. Find the second occurrence
| A | B | |
|---|---|---|
| 1 | ID | |
| 2 | HRD-0932-B | 9 |
The optional third argument starts the search later. The first dash in "HRD-0932-B" is at 4, so starting at 5 finds the second dash at position 9.
Common FIND mistakes
- #VALUE! when the text is not found. FIND returns an error rather than 0 when nothing matches. Wrap it in IFERROR to return a fallback, or use ISNUMBER(FIND(...)) as a yes-or-no test.
- Case mismatch. "west" is not found in "West". Use SEARCH for case-insensitive matching.
- Using wildcards. FIND treats * and ? as ordinary characters. SEARCH supports wildcards.
- Forgetting the position is not the length. To take the text before a character, subtract 1:
LEFT(A2,FIND(" ",A2)-1).
FIND on Excel job tests
FIND sits inside most variable-length extractions: the first name before the space, the domain after the at sign, the product code between dashes.
Build the formula in two steps. Write FIND on its own in a helper cell and confirm the number, then nest it in LEFT, MID or RIGHT. A row with no separator returns #VALUE!, so wrap it in IFERROR if the task says to handle those.
FIND is case sensitive and has no wildcards. If the capitalization in the data is mixed, SEARCH is the one you want.
Related functions
FIND questions
- What is the difference between FIND and SEARCH?
- FIND is case sensitive and does not allow wildcards. SEARCH ignores case and accepts ? and *.
- How do I check whether a cell contains some text?
=ISNUMBER(FIND("text",A2))returns TRUE or FALSE. Use SEARCH for case-insensitive.- What does FIND return when the text is missing?
- #VALUE!.
- How do I find the second occurrence?
- Use the third argument to start after the first one.
Practice FIND
The lesson FIND and SEARCH: locate text (module 9 of Everyday Excel) has checked FIND 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