FINDB

What it does

FINDB(find_text, within_text, [start_num])

FINDB locates one text string inside another and returns the starting position of the first occurrence, exactly as FIND does — but the position is counted in bytes rather than in characters. It is case sensitive and does not allow wildcards, on the same terms as FIND.

Microsoft's article states the distinction more precisely than any other article in the byte-variant set, and this page quotes its content rather than reconstructing it: FIND is intended for use with languages that use the single-byte character set (SBCS), whereas FINDB is intended for languages that use the double-byte character set (DBCS). FIND always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is. FINDB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language.

That last clause is the part worth reading twice, because it makes FINDB's answer depend on three things rather than one:

1. the text — how many of its characters are double-byte under the host encoding;
2. the host's enabled editing languages;
3. which of them is set as the default language.

Change none of the text and only the third, and the same formula returns a different number. This is the sharpest statement in Microsoft's documentation that the byte variants are environment-dependent functions, and it is why the Handbook publishes FINDB as a separate entry rather than as a note on FIND. (Seven published rows document a byte-variant pair this way; splitting all seven is the whole of the difference between 534 published Excel rows and 541 Handbook entries.)

Two consequences follow directly. When the DBCS condition is not met, FINDB counts every character as 1 and its results coincide with FIND's — so an all-ASCII or Western-host test suite cannot distinguish the two functions at all. And when it is met, a returned byte position is not a valid character position: feeding a FINDB result into MID rather than MIDB is a byte/character unit error that produces plausible wrong answers rather than errors.

The article text retrieved for this page states the DBCS condition in terms of enabled and default editing languages, and does not, in that text, enumerate which languages support DBCS. This page therefore does not name them.

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, the character at which to start the search; the first character in within_text is character number 1, and omitting it is documented as assuming 1. Note that the article's argument description speaks of characters for this argument while the returned position is in bytes for FINDB; whether start_num is interpreted in bytes or characters under the DBCS condition is a real question that the retrieved text does not resolve.

As with FIND, the needle comes first. FINDB(within, needle) is a silent bug.

Try it

Quirks

Result and edge cases

The return kind is Number.

• A start_num or a result can fall inside a character. This is the byte-variant hazard with no character-variant analogue: byte position 2 of a string beginning with a double-byte character is the second half of that character. What FINDB does when a match would begin there, and what it does when start_num names such a position, is undefined by every source this page can cite.
• Under the non-DBCS condition the function coincides with FIND, per the article's own statement.
• Astral characters have no natural DBCS byte length at all, and the Compatibility Version feature that Microsoft documents for surrogate handling elsewhere in the text family has no stated interaction with byte counting.

Errors

• #VALUE! when find_text does not appear in within_text — stated in the article for both members of the pair.
• Microsoft publishes a dedicated article on correcting #VALUE! in FIND/FINDB and SEARCH/SEARCHB.
• Whether an out-of-range or mid-character start_num errors, and with which code, is not stated in the retrieved text.
• 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. Do not implement FINDB as FIND. They coincide only under the non-DBCS condition, and aliasing them ships that specialisation. Say so if that is what you are shipping.
2. The DBCS condition is host state, not text state. An implementation faithful to the documentation needs the enabled-languages and default-language settings as inputs, which makes this function's observation context wider than an Excel build and a platform.
3. Byte positions and character positions must not be interchanged internally, or the bug the function exists to avoid reappears inside the implementation.
4. Write down the mid-character rules for both start_num and the returned position.

Rust code

21 findb_fn "FINDB" (2, 3) [V "find_text", V "within_text", V "start_num"] |s, a, sv| { single_byte(s)?; find(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

• FIND is the character-counting partner, documented in the same article. It is the function to use unless you specifically need byte offsets.
• SEARCHB is the case-insensitive, wildcard-aware byte variant — FINDB is to SEARCHB as FIND is to SEARCH.
• LEFTB, RIGHTB, MIDB, LENB, REPLACEB are the rest of the byte-variant family, and are the functions a FINDB result must be fed into for the units to agree. Note that the LEFT and LEN articles now mark their byte variants deprecated and no longer state the DBCS mechanism at all; this article still does, which makes it the best surviving documentation of what byte counting means anywhere in the family.
• ASC and DBCS convert between half-width and full-width forms.

Named variants

No named variants are recorded yet.