MIDB
Returns a specific number of characters from a text string, starting at a given byte position. Each double-byte character (DBCS) counts as 2 bytes; single-byte characters count as 1.
MIDB(text, start_num, num_bytes)Arguments
textThe text from which to extract a substring.start_numThe byte position of the first byte to extract (1-based).num_bytesThe number of bytes to return.
=MIDB("Excel",2,3)Returns 3 bytes starting at byte 2
=MIDB("日本語",3,2)Starts at byte 3, returns 2 bytes (one DBCS char)
=MIDB("AB日本",3,2)Starts at byte 3, returns 2 bytes (one DBCS char)
- •On systems without DBCS support, MIDB behaves like MID
- •Use MID to extract by character position instead of byte position
- •Mainly used in East Asian Excel installations
- •Using MIDB when MID (character-based extraction) is actually what's needed - MIDB counts bytes for both start_num and num_bytes, so values that seem right by character position can land in the middle of a double-byte character
- •Miscounting start_num as a character index rather than a byte index, which produces an off-by-one or garbled result whenever the text contains any double-byte characters before the target position
- •Forgetting that on systems without double-byte character set support, MIDB simply behaves like MID, so testing on a Western-locale machine can hide bugs that only appear with East Asian text
MIDExtracts by character position and count instead of byte position and count, the correct choice for text that doesn't need double-byte-aware handling.LEFTBA specialized case of the same byte-based extraction as MIDB, always starting from the first byte.RIGHTBAnother specialized case of byte-based extraction, always counting from the end of the string instead of a specific start position.Why did MIDB return a garbled or unexpected character?
start_num or num_bytes probably landed in the middle of a double-byte character - since each such character occupies 2 bytes, byte positions that don't align with character boundaries produce broken results.
Should I use MID or MIDB?
Use MID for ordinary character-based extraction. Reach for MIDB only when you specifically need byte-based positioning, such as matching a legacy system's storage limits for East Asian text.
Does MIDB work the same as MID on a system without double-byte language support?
Yes, in that case there's no distinction between characters and bytes, so MIDB and MID return identical results.
Need to translate a formula using MIDB?
Use our translator to convert your complete formula