FIND

What it does

FIND(find_text, within_text, [start_num])

FIND locates one text string inside another and returns the 1-based position at which the first occurrence starts, counted from the first character of the containing string. Microsoft's article states it that way: the number of the starting position of find_text, counted from the first character of within_text.

Two properties in the article's own words make FIND the strict member of the pair: FIND is case sensitive and does not allow wildcard characters. It is therefore a literal search — no folding, no pattern language, no locale-aware matching. That is the whole of its semantics, and it is exactly why it is the right tool for parsing machine-generated text and the wrong tool for matching what a human typed.

Stated as an operation: return the smallest position p ≥ start_num such that the characters of within_text beginning at p equal find_text, comparing character by character with no transformation of either side. Positions are counted in characters, and the article is explicit that FIND "always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is" — a guarantee FINDB deliberately does not make.

Arguments

find_text — required, the text you want to find.

within_text — required, the text containing the text you want to find.

start_num — optional, specifying the character at which to start the search. The first character in within_text is character number 1, and omitting start_num is documented as assuming 1.

The argument order is the position readers get wrong most often, and it is worth stating plainly: the needle comes first. FIND(within, needle) is a silent bug, not an error, whenever the two strings happen to contain each other.

start_num has two ordinary uses that are worth separating: skipping a known prefix, and finding the second occurrence by feeding it the position of the first plus one. The second use is the reason the argument exists.

Try it

Quirks

Result and edge cases

The return kind is Number, a positive integer when the search succeeds.

• The result is absolute, not relative to start_num. FIND("a","banana",4) reports the position within the whole string, not the offset from position 4. Every off-by-one bug in occurrence-walking formulas comes from assuming otherwise.
• The search is for the first occurrence at or after start_num. There is no direction argument and no last-occurrence form; finding the last occurrence is the classic SUBSTITUTE-with-a-sentinel idiom.
• Case sensitivity is absolute, including for characters whose case difference is not an ASCII letter pair. Whether Excel's comparison is code-unit equality or something normalising is not stated in the article and has not been checked here — the same open question that EXACT raises.
• Empty find_text. What FIND("", t) returns is not stated in the text of the article retrieved for this page. It is one of the two or three most commonly relied-on edge cases in the function, and the Handbook does not assert a value for it.
• Numbers and logicals convert to text before searching, so FIND(1, 2140) searches the rendering 2140 for the rendering 1.

Errors

• #VALUE! when find_text does not appear in within_text — stated in the article. This is the design decision that shapes every formula built on FIND: absence is an error, not a zero and not #N/A. Wrapping in IFERROR, or switching to TEXTBEFORE/TEXTAFTER which take an if-not-found argument, is the standard response.
• Microsoft publishes a dedicated article on correcting #VALUE! in FIND/FINDB and SEARCH/SEARCHB, which is a fair indication of how often the not-found error surprises people.
• The conventional expectation is that start_num values less than 1, or greater than the length of within_text, also produce #VALUE!. Those conditions are not confirmed in the article text retrieved for this page, and the Handbook does not assert them.
• Error inputs propagate, and a value kind with no text conversion surfaces #VALUE! under the shared coercion rules (chapter 02).

How to implement

Notes for implementers

1. 1-based, absolute, first-match. All three are separately observable and all three are easy to get wrong in a port from a 0-based language.
2. The empty-needle case needs an explicit rule, and it should be tested against Excel rather than reasoned about: both "matches immediately at start_num" and "errors" are defensible and the documentation retrieved here settles neither.
3. Do not fold, do not normalise, do not treat * or ? as special. FIND's value is that it is the function which does none of those things.
4. Comparison unit must match the slicing family's unit, or positions returned by FIND will not be valid inputs to MID on astral text.

Rust code

20 find_fn "FIND" (2, 3) [V "find_text", V "within_text", V "start_num"] |s, a, sv| { find(s, a, false) }

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Recorded variants

Alternatives

Relationships

• SEARCH is the permissive sibling: case-insensitive and wildcard-aware. The pair FIND/SEARCH is the same distinction as EXACT versus =. Choosing wrongly between them is the most common defect in text-parsing formulas, because both return plausible numbers.
• FINDB is the byte-counting variant, documented in the same article; its positions are byte positions rather than character positions.
• SEARCHB completes the square.
• MID, LEFT, RIGHT are what the returned position is almost always fed into; the -1 and +1 adjustments in those compositions are where FIND's 1-based, absolute result matters.
• TEXTBEFORE, TEXTAFTER, TEXTSPLIT are the modern replacements for the whole LEFT/MID+FIND idiom. They are not deprecating FIND, but a formula that only needs the text around a delimiter should not be computing a position at all.
• SUBSTITUTE and REPLACE share this entry's implementing module in the reference engine and share the same position conventions.

Named variants

No named variants are recorded yet.