SEARCHB

What it does

SEARCHB(find_text, within_text, [start_num])

SEARCHB(find_text, within_text, [start_num]) locates find_text inside within_text and returns the 1-based byte position at which it starts, counted from the beginning of within_text.

Everything that makes SEARCH what it is carries over unchanged: the match is not case sensitive, and find_text is a wildcard pattern in which ? matches any single character, * matches any sequence, and ~ escapes a following ? or *. The B in the name changes the coordinate system of the answer and the coordinate system of start_num. It does not change the matching rule.

And the coordinate change itself is conditional. Microsoft's rule for the whole B family:

The B functions count each double-byte character as 2 only when a DBCS language has been enabled for editing and set as the default language. Otherwise they count each character as 1.

So on a non-DBCS-default installation SEARCHB and SEARCH return the same numbers for the same inputs. On a Japanese-, Chinese- or Korean-default installation they return different numbers, because the same match sits at a different byte offset than character offset. The active default language is an unlisted input to this function.

Arguments

start_num is in bytes, which is the argument most likely to be wrong in a formula ported from SEARCH. A start_num computed from LEN is a character coordinate; feeding it to SEARCHB is a unit error that produces plausible wrong answers rather than an error value. LENB is the function that produces the right coordinates.

Try it

Quirks

SEARCHB versus FINDB

The same two-axis distinction as the character pair, restated because it is exactly as confusable at the byte level:

Choosing SEARCHB over FINDB for the case-insensitivity and then passing a find_text that contains a literal * is the same trap it is on the character functions, and it is no easier to spot here.

Result and edge cases

Returns Number — a 1-based byte position.

The behaviours Microsoft documents for SEARCH are stated on the same page for SEARCHB: find_text not found is #VALUE!, and a start_num outside the valid range is #VALUE! — with the range measured in bytes here.

The distinctively SEARCHB questions, none of which the documentation answers:

• Does start_num have to fall on a character boundary? An odd start_num inside a double-byte character is reachable by ordinary arithmetic and is undefined here.
• Does ? match one byte or one character? Under DBCS these differ, and the wildcard language is documented in terms of characters while the result is measured in bytes. This is the sharpest unresolved question on the page.
• Is the returned position always on a character boundary? It should be if matches only begin at character boundaries, but that is an inference from a rule nobody has stated.

Errors

As documented on Microsoft's SEARCH, SEARCHB page:

Shared coercion errors and error propagation apply as elsewhere. The Handbook has not verified any of this against Excel.

How to implement

Notes for implementers

The function has to hold two coordinate systems at once: the match runs over characters (the wildcard language is defined in characters) and the answer is reported in bytes. That mapping has to be built from the same code-page tables LENB uses, or the family will not agree with itself.

The ?-matches-what question is not an implementation detail to be settled quietly. Pick an answer, document it, mark it unverified, and make it consistent with whatever MIDB does at partial-character boundaries.

A SEARCHB test vector without the default editing language recorded is not a test vector.

Rust code

23 searchb_fn "SEARCHB" (2, 3) [V "find_text", V "within_text", V "start_num"] |s, a, sv| { single_byte(s)?; search(s, a, true) }

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.

Alternatives

Relationships

• SEARCH — the character-position twin, documented by Microsoft on the same page and published by the Handbook as its own entry.
• FINDB — the case-sensitive, literal byte locator. See the comparison above.
• LENB, MIDB, REPLACEB, LEFTB, RIGHTB — the rest of the B family. SEARCHB is the family's coordinate producer: its output is exactly what MIDB and REPLACEB expect as start_num, and mixing a SEARCH result into a MIDB call is the classic unit mismatch.
• SUBSTITUTE — there is no SUBSTITUTEB, and that is not an oversight: by-content substitution never needs byte coordinates. Under DBCS it is usually the safer tool.

Named variants

No named variants are recorded yet.