Documentation/Calc Functions/MIDB

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎Nederlands



Function name:

MIDB

Category:

Text

Summary:

Extracts a sub-string from the middle of the supplied text string, with the start position and length of the sub-string specified in bytes.

Syntax:

MIDB(Text; Start; Number of Bytes)

Returns:

Returns a text string comprising the specified number of bytes from the specified position within the supplied text string.

Arguments:

Text is a text string (in quotation marks), a number, or a reference to a cell containing one of those types, that is the string from which characters are to be extracted.

Start is a positive integer, or a reference to a cell containing a positive integer, that is the position of the first byte to be extracted.

Number of Bytes is a non-negative integer, or a reference to a cell containing a non-negative integer, that is the number of bytes to be extracted.

  • If either Start or Number of Bytes is non-numeric, then MIDB reports a #VALUE! error.
  • If either Start or Number of Bytes is a non-integer value, then MIDB truncates it to an integer value.
  • If after truncation Start is less than 1, then MIDB reports an invalid argument error (Err:502).
  • If after truncation Start is greater than the length (in bytes) of Text, then MIDB returns the empty string.
  • If Number of Bytes is less than 0, then MIDB reports an invalid argument error (Err:502).
  • If after truncation Number of Bytes is equal to 0, then MIDB returns the empty string.
  • If (Start + Number of Bytes – 1) is greater than the length (in bytes) of Text, then MIDB returns a string comprising all bytes from Start to the end of Text.

Additional details:

Details specific to MIDB function

If the specified number of bytes would otherwise lead to extracting only one byte of a two-byte character, MIDB instead inserts a space character (Unicode U+0020) at the end of the returned string.

Note that =MIDB(text; 1; length) is equivalent to =LEFTB(text; length)


General information about byte-position functions

Note pin.svg

Note:
For convenience, the information in this subsection is repeated on all pages describing Calc’s byte-position functions.

Calc provides several byte-position text functions, which are intended to be used with double-byte character set (DBCS) languages. These functions are FINDB, LEFTB, LENB, MIDB, REPLACEB, RIGHTB, and SEARCHB. Each has an equivalent "ordinary" text function with a similar name but without the final letter "B".

Calc considers double-byte characters to be those characters lying within the following Unicode code blocks:


  • Bopomofo Extended (U+31A0 ... U+31BF)
  • Bopomofo (U+3100 ... U+312F)
  • CJK Compatibility Forms (U+FE30 ... U+FE4F)
  • CJK Compatibility Ideographs Supplement (U+2F800 ... U+2FA1F)
  • CJK Compatibility Ideographs (U+F900 ... U+FAFF)
  • CJK Compatibility (U+3300 ... U+33FF)
  • CJK Radicals Supplement (U+2E80 ... U+2EFF)
  • CJK Strokes (U+31C0 ... U+31EF)
  • CJK Symbols and Punctuation (U+3000 ... U+303F)
  • CJK Unified Ideographs Extension A (U+3400 ... U+4DBF)
  • CJK Unified Ideographs Extension B (U+20000 ... U+2A6DF)
  • CJK Unified Ideographs (U+4E00 ... U+9FFF)
  • Enclosed CJK Letters and Months (U+3200 ... U+32FF)
  • Halfwidth and Fullwidth Forms (U+FF00 ... U+FFEF)
  • Hangul Compatibility Jamo (U+3130 ... U+318F)
  • Hangul Jamo (U+1100 ... U+11FF)
  • Hangul Syllables (U+AC00 ... U+D7AF)
  • High Private Use Surrogates (U+DB80 ... U+DBFF)
  • High Surrogates (U+D800 ... U+DB7F)
  • Hiragana (U+3040 ... U+309F)
  • Ideographic Description Characters (U+2FF0 ... U+2FFF)
  • Kanbun (U+3190 ... U+319F)
  • Kangxi Radicals (U+2F00 ... U+2FDF)
  • Katakana (U+30A0 ... U+30FF)
  • Low Surrogates (U+DC00 ... U+DFFF)
  • Private Use Area (U+E000 ... U+F8FF)
  • Yi Radicals (U+A490 ... U+A4CF)
  • Yi Syllables (U+A000 ... U+A48F)


See Wikipedia's Unicode block page for more information about these blocks.

In addition, code points U+005C and U+20AC are taken to be double-byte characters if the system language is set to Japanese.

As noted in ODF 1.2, byte positions are implementation-dependent and so reliance upon them reduces interoperability.


Examples:

The following examples are similar to those used for the MID function and demonstrate the consistent behavior of the two functions for single-byte character strings.

Formula Description Returns
=MIDB("Input string"; 2; 3) Here the function returns three bytes from the supplied string, starting at the second byte. "npu"
=MIDB(12345.6789; 5; 4) Here the function returns four bytes from the supplied number, starting at the fifth byte, and formats the result as text. "5.67"
=MIDB(D1; D2; D3) where cell D1 contains the string "Input string", cell D2 contains the number 2, and cell D3 contains the number 3. Here the function returns three bytes from the supplied string, starting at the second byte. "npu"
=MIDB("Input string"; 6; 0) Here the function returns the empty string. ""

The remaining examples demonstrate the behavior of MIDB for strings that include double-byte characters. These examples utilize the double-byte characters "中" (Unicode CJK Unified Ideograph-4E2D) and "国" (CJK Unified Ideograph-56FD).

Formula Description Returns
=MIDB("中国"; 1; 0) Here the function returns the empty string, because the Number of Bytes argument is set to 0. ""
=MIDB("中国"; 1; 1) Here the function returns a space character because one byte is only half of a DBCS character. " "
=MIDB("中国"; 1; 2) Here the function returns "中" because two bytes constitute a complete DBCS character. "中"
=MIDB("中国"; 1; 3) Here the function returns "中 " because three bytes constitute one and a half DBCS characters; the last byte returned is a space character. "中 "
=MIDB("中国"; 1; 4) Here the function returns "中国" because four bytes constitute two complete DBCS characters. "中国"
=MIDB("中国"; 2; 1) Here the function returns a space character because byte position 2 is not at the beginning of a character in this DBCS string. " "
=MIDB("中国"; 2; 2) Here the function returns two space characters because the two bytes requested correspond to the last half of the first character and the first half of the second character in the DBCS string. " "
=MIDB("中国"; 2; 3) Here the function returns " 国" because byte position 2 is not at the beginning of a character in the DBCS string and so a space character is returned for byte position 2. " 国"
=MIDB("中国"; 3; 1) Here the function returns a space character because byte position 3 is at the beginning of a character in the DBCS string and one byte is only half a DBCS character. " "
=MIDB("中国"; 3; 2) Here the function returns "国" because byte position 3 is at the beginning of a character in the DBCS string, and two bytes constitute one DBCS character. "国"

Related LibreOffice functions:

FINDB

LEFTB

LENB

MID

REPLACEB

RIGHTB

SEARCHB

ODF standard:

Section 6.7.5, part 2

Equivalent Excel functions:

MIDB