SEARCH

What it does

SEARCH(find_text, within_text, [start_num])

SEARCH(find_text, within_text, [start_num]) returns the 1-based position, counted from the start of within_text, of the first place at or after start_num where find_text matches.

Two properties define it, and both are in Microsoft's documentation:

1. The match is not case sensitive.
2. find_text is a wildcard pattern, not a literal: ? matches any single character, * matches any sequence of characters, and ~ escapes the next ? or * so it can be matched literally.

The second property is the one that gets missed. SEARCH is not "FIND with the case switched off" — it is a small pattern matcher. Any find_text that came from user data or from another cell may contain * or ?, and if it does, SEARCH will not look for it literally.

The returned position is measured from the beginning of within_text in both cases, regardless of start_num. start_num moves where the scan begins; it does not move the origin of the coordinate system. SEARCH("a", "abca", 2) is 4, not 3.

Arguments

Microsoft describes start_num as the way to skip a specified number of characters — the documented use case is finding the second occurrence of something by restarting the scan past the first.

start_num is a numeric slot subject to ordinary to-number coercion; find_text and within_text are text slots. See Coercion and lifting.

Try it

Quirks

SEARCH versus FIND

This is the most-confused pair in the text category. The two functions have the same signature and different semantics on two axes at once:

Microsoft states the case axis explicitly on the SEARCH page — if you want a case-sensitive search, use FIND — and documents the wildcard set on the same page.

The practical selection rule:

• Matching a substring exactly as given, including a literal * or ? — use FIND.
• Matching case-insensitively, or matching a pattern — use SEARCH.
• Matching case-insensitively but needing find_text treated literally — you need SEARCH with find_text escaped, or FIND over UPPERed copies of both arguments. There is no single function that does it.

Wildcards

The documented pattern language is three characters:

What the documentation does not settle, and this page will not invent:

• Whether the match is leftmost-shortest or leftmost-longest when * could match several ways. Since only the start position is returned, the difference is usually invisible — but not always, and it is unverified.
• Whether ~ before a character that is not ?, * or ~ is an error, is dropped, or is matched literally.
• Whether a find_text consisting only of * returns start_num, 1, or something else.

Each of these is on the probe list below.

Result and edge cases

Returns Number — a 1-based character position.

Documented behaviours:

• find_text not found → #VALUE!. There is no "not found" sentinel and no zero return. Wrapping in IFERROR is the conventional guard.
• start_num outside 1 … LEN(within_text) → #VALUE!.

Not documented, and therefore left open here: what SEARCH("", A1) returns. An empty pattern matching at the current position is the usual convention and would give start_num, but Microsoft's page does not say so and the Handbook has not checked.

"Character" is a UTF-16 code unit, consistent with LEN and the rest of the text family; see The value universe.

Errors

As documented on Microsoft's SEARCH, SEARCHB page:

Non-numeric text in start_num and non-convertible values in the text slots surface #VALUE! under the shared coercion rules, and error values in any argument propagate. The Handbook has not verified any of this against Excel.

How to implement

Notes for implementers

The wildcard engine is the whole implementation. Case folding is the easy half; deciding what * and ? mean over UTF-16 code units is the hard half, because ? matching "any single character" has to choose between one code unit and one scalar value, and those differ on every astral character.

Case-insensitivity is locale-sensitive in the same way PROPER's capitalization is: Turkish dotted and dotless i, and full-versus-simple case folding, both change what matches. An invariant fold is a defensible default and is a decision to record, not a neutral one.

start_num bounds are checked before the search, and the check is against LEN(within_text), not against LEN(within_text) + 1, so scanning from just past the end is documented as an error rather than as a not-found. That is a genuine asymmetry with the way many string libraries behave.

The returned position is absolute, not relative to start_num. An implementation that runs the search over a suffix has to add the offset back.

Rust code

22 search_fn "SEARCH" (2, 3) [V "find_text", V "within_text", V "start_num"] |s, a, sv| { search(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

• FIND — the case-sensitive, literal counterpart. See the comparison table above. Neither supersedes the other; both are current.
• SEARCHB — the byte-position sibling, documented by Microsoft on the same page and published by the Handbook as its own entry.
• SUBSTITUTE — the by-content editor. Note the asymmetry that trips people: SEARCH is case-insensitive and takes wildcards, while SUBSTITUTE is case-sensitive and takes none. A formula that locates with SEARCH and edits with SUBSTITUTE is using two different matching rules on the same string.
• REPLACE — the by-position editor, and the usual consumer of a SEARCH result.
• ISNUMBER(SEARCH(x, y)) is the idiomatic case-insensitive "contains" test, precisely because SEARCH errors rather than returning zero.
• XMATCH with its wildcard match mode does pattern matching over a lookup array rather than within a single string — a different job that reuses the same wildcard vocabulary.
• REGEXTEST / REGEXEXTRACT are the modern route when the three-character wildcard language is not enough.

Named variants

No named variants are recorded yet.