The Excel FIND function, explained interactively.
FIND returns the 1-based position of the first occurrence of one string inside another. It’s a locator, not an extractor — on its own FIND gives you a number, not a piece of text. The payoff comes when you pair it with LEFT, MID, or RIGHT to extract text around delimiters whose position varies row to row.
How to use FIND
- Type
=FIND(and supply the find_text — the substring to locate. Usually a single character like"@"or"-". - Add the within_text — the source string, almost always a cell reference (
A2). - Optionally add start_num — the position to begin searching. Default is
1. Skip past the first match to find the second. - Close the parenthesis. FIND returns a number. For missing matches, wrap with IFERROR to avoid
#VALUE!:=IFERROR(FIND("@", A2), 0).
In the demo below, the found character is highlighted in the source column so you can see exactly where FIND landed. Flip find_text from "@" to "." and watch how the positions shift — FIND stops at the first dot, not the last.
find_text inside within_text. Case-sensitive. Errors with #VALUE! when nothing matches."@" or "-", but can be any string. Empty string "" returns 1.start_num.1 (the start). Set it past the first match to find the second, third, etc. Values < 1 return #VALUE!.| A | B | |
|---|---|---|
| 1 | Position of "@" | |
| 2 | ava@formulagym.com | 4 |
| 3 | ben.carson@example.co.uk | 11 |
| 4 | chloe@sub.domain.io | 6 |
| 5 | dev-team@company.com | 9 |
| 6 | eva99@mail.net | 6 |
| 7 | finn_gus@example.org | 9 |
=FIND("@", A2, 1). The highlighted character in column A is what FIND located; the number in column B is its 1-based position. find_text = "@", start_num = 1: @ is the email split point — pair with LEFT to extract the local part, or with MID for the domain.FIND syntax and arguments
Two required arguments, one optional. See Microsoft’s official FIND / FINDB reference for the canonical specification.
SEARCH for wildcard or case-insensitive matching. An empty string "" returns 1, matching the first character position.within_text to begin searching. Defaults to 1 (the first character). Set it past a previous match’s position to find the next occurrence. Values less than 1 or past the text length return #VALUE!.FIND examples
Four patterns that cover most real-world FIND use.
Example 1: FIND + LEFT — extract email local part
The canonical FIND pattern. Locate the @ sign, then take everything before it.
FIND returns the position of @; LEFT takes everything before it; -1 drops the @ itself. Returns "ava" from "ava@formulagym.com". Wrap with IFERROR for rows where the email might be blank or malformed.
Example 2: FIND + MID — extract email domain
Mirror of example 1 — same FIND, different extractor. MID starts just after the @ and takes a comfortably large length that FIND-less MID would clamp.
+1 skips past the @; 99 is a generous upper bound that MID silently clamps to the remainder of the string. The forgiving length behaviour is what makes this idiom work without knowing domain length in advance.
Example 3: FIND — locate the second occurrence
Nest two FINDs to skip past the first hit and find the next. Useful for multi-dashed or multi-dotted identifiers.
The inner FIND("-", A2) returns the first dash’s position; +1 shifts past it; the outer FIND starts there. Chain a third FIND for the fourth dash. For arbitrary Nth matches, build up with SUBSTITUTE replacing the Nth copy with a unique sentinel.
Example 4: FIND — validate that a character exists
A presence check. Combined with IFERROR and IF, FIND becomes a case-sensitive contains-test.
IFERROR converts FIND’s #VALUE! into 0; the > 0 test flips that into a boolean. For case-insensitive version, swap FIND for SEARCH. Simpler than regex, more precise than COUNTIF with wildcards.
Common FIND errors and fixes
Four failure modes, each with what to check and how to recover.
FIND returns #VALUE! when there’s no match
Cause: find_text does not occur in within_text. FIND has no soft-miss behaviour — any unmatched call throws #VALUE!.
Wrap with IFERROR: =IFERROR(FIND("@", A2), 0). Returns 0 for rows without an@, letting downstream formulas treat missing matches as a sentinel value.
FIND returns the wrong position (case mismatch)
Cause: FIND is case-sensitive — FIND("A", "banana") is #VALUE! because there’s no uppercase A. Common gotcha with user-entered data.
Switch to SEARCH for case-insensitive matching — it’s otherwise identical in syntax and behaviour. Or normalise with LOWER(A2) before FIND if you need both parts lowercase.
FIND’s wildcards don’t work
Cause: FIND treats * and ? literally — no pattern expansion. Unlike COUNTIF, MATCH, or SEARCH, FIND is a literal-string matcher.
Use SEARCH when wildcards are needed. Alternatively, precompute the position with another formula (e.g. MATCH, SUBSTITUTE) and feed the result into FIND for literal matching thereafter.
FIND misaligns positions on CJK / DBCS text
Cause: legacy DBCS Excel counts each double-byte character as 2 for byte-oriented builds. Mixing FIND with MID in those builds can offset the read.
In modern Unicode Excel (365 / 2021+), FIND is character-accurate — same as MID and LEFT. For legacy interop, use FINDB and pair with MIDB / LEFTB consistently. Never mix byte- and character-indexed functions in one chain.
FIND vs SEARCH, LEFT & MID
FIND is the locator; LEFT / MID / RIGHT are extractors. SEARCH is the case-insensitive version of FIND.
| Function | Returns | Case sensitive? | Wildcards? |
|---|---|---|---|
| FIND | Position of substring | Yes | No — literal match |
| SEARCH | Position of substring | No | Yes — ? and * |
| LEFT | Text from the start | n/a | n/a |
| MID | Text from a position | n/a | n/a |
Rule of thumb: FIND when case matters or you need literal matching; SEARCH when you want fuzzy user-entered queries with wildcards. Both pair identically with LEFT / MID / RIGHT — the extractor doesn’t care which locator produced the position. For Excel 365, TEXTBEFORE / TEXTAFTER combine locate-and-extract into one call.
FIND frequently asked questions
6.01Why does my FIND formula return #VALUE!?▸
Because find_text doesn’t appear in within_text, or start_num is less than 1 or past the end. FIND has no soft-miss behaviour — any unmatched call throws #VALUE!. Wrap with IFERROR to return a fallback: =IFERROR(FIND("@", A2), 0).
6.02What’s the difference between FIND and SEARCH?▸
FIND is case-sensitive and doesn’t accept wildcards. SEARCH is case-insensitive and supports ? and * wildcards. Use FIND when you need strict matching (case matters, or you’re searching for a literal * or ?). Use SEARCH for fuzzy user-entered queries.
6.03How do I find the second occurrence of a character with FIND?▸
Nest a second FIND: =FIND("-", A2, FIND("-", A2) + 1). The inner FIND returns the first dash’s position, +1 skips past it, and the outer FIND starts there. For arbitrary Nth occurrences, build up with FIND / SUBSTITUTE combinations.
6.04Is FIND case-sensitive in Excel?▸
Yes — FIND treats "A" and "a" as different characters. FIND("A", "banana") returns #VALUE! because there’s no uppercase A. If you want case-insensitive matching, use SEARCH, which is otherwise identical.
6.05Can FIND use wildcards like * or ?▸
No — FIND matches literally. A literal asterisk or question mark is taken at face value, no wildcard expansion. Use SEARCH when wildcards are needed, or use FIND combined with SUBSTITUTE if you need to find one of several characters.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.