Documentation/Calc Functions/REPLACEB

    From The Document Foundation Wiki

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

    Other languages:


    Function name:

    REPLACEB

    Category:

    Text

    Summary:

    Replaces bytes in a text string with the bytes of a different text string.

    Syntax:

    REPLACEB(Text; Position; Length; New Text)

    Returns:

    Returns a text string that corresponds to the original text string but with the specified bytes replaced by those of the supplied new 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 original string containing bytes to be replaced.

    Position is a positive integer, or a reference to a cell containing a positive integer, that is the position within Text of the first byte to be replaced.

    Length is a non-negative integer, or a reference to a cell containing a non-negative integer, that is the number of bytes within Text to be replaced.

    New Text is a text string (in quotation marks), a number, or a reference to a cell containing one of those types, that is the new string containing the replacement bytes. It is not necessary for the length of New Text to be the same as the value of Length. Set New Text to the empty string to delete rather than replace bytes in Text.

    • If either Position or Length is non-numeric, then REPLACEB reports a #VALUE! error.
    • If either Position or Length is a non-integer value, then REPLACEB truncates it to an integer value.
    • If Position is less than 1 or Length is less than 0, then REPLACEB reports an invalid argument error (Err:502).
    • If Position is greater than the length of Text, then REPLACEB reports an invalid argument error (Err:502).
    • If (Position + Length - 1) is greater than the length of Text, then REPLACEB reports an invalid argument error (Err:502).
    • If Length is equal to 0, no bytes are replaced. However, the bytes of New Text are inserted before the byte that is originally at the position given by Position.

    Additional details:

    Details specific to REPLACEB function

    The formula:

    =REPLACEB(Text; Position; Length; New Text)

    is equivalent to:

    =LEFTB(Text; Position-1) & New Text & RIGHTB(Text; LENB(Text) - (Position-1) - Length)

    If the specified number of bytes to be replaced would otherwise lead to replacing only one byte of a two-byte character, REPLACEB instead inserts a space character (Unicode U+0020) in the returned string. For example, the formula =REPLACEB("ｔｅｓｔ"; 1; 1; "x") returns "x ｅｓｔ", having replaced the initial double-byte "ｔ" character (U+FF54) with a lowercase x (U+0078) followed by a space (U+0020). The formula =REPLACEB("ｔｅｓｔ"; 2; 1; "x") returns " xｅｓｔ", having replaced the initial double-byte "ｔ" character (U+FF54) with a space (U+0020) followed by a lowercase x (U+0078).

    You can pass the Text and New Text arguments as numbers but REPLACEB always returns text. If you intend to perform further calculations on a number that has been converted to text, you will need to convert it back to a number (for example, using either the VALUE function or the NUMBERVALUE function).


    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:

    Most of the following examples are similar to those used for the REPLACE function and demonstrate the consistent behavior of the two functions for single-byte character strings (SBCS).

    Formula Description Returns
    =REPLACEB("mouse"; 2; 3; "ic") Here the function replaces three bytes starting at byte 2 (SBCS characters "ous") with two SBCS characters ("ic"), and returns the string "mice". "mice"
    =REPLACEB(D1; D2; D3; D4) where cell D1 contains the string "mouse", cell D2 contains the value 2, cell D3 contains the value 3, and cell D4 contains the string "ic". Here the function again replaces three bytes starting at byte 2 (SBCS characters "ous") with two SBCS characters ("ic"), and returns the string "mice". "mice"
    =REPLACEB("1234567"; 1; 1; "444") Here the function replaces one byte starting at byte 1 (SBCS character "1") with three SBCS characters ("444"), and returns the string "444234567". "444234567"
    =REPLACEB(1,234,567; 1; 1; 444) In this example the original and new text strings are passed as numeric arguments, without quotation marks. Here the function again replaces one byte starting at byte 1 (SBCS character "1") with three SBCS characters ("444"), and returns the string "444234567". "444234567"
    =REPLACEB("Input string"; 7; 0; "text ") Here the function replaces no bytes but inserts a five byte string (SBCS characters "text ") immediately before byte 7 (SBCS character "s"), returning the string "Input text string". "Input text string"
    =REPLACEB("Hello xxxx!"; 6; 5; "") Here the function replaces five bytes starting at byte 6 (SBCS characters " xxxx") with the empty string (effectively deleting the five SBCS characters), returning the string "Hello!". "Hello!"

    The remaining examples demonstrate the behavior of REPLACEB 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
    =REPLACEB("中国"; 1; 0; "?") Here the function returns the string "?中国", which is five bytes long. "?中国"
    =REPLACEB("中国"; 1; 1; "?") Here the function returns the string "? 国" (note the space after the question mark), which is four bytes long. "? 国"
    =REPLACEB("中国"; 1; 2; "?") Here the function returns the string "?国", which is three bytes long. "?国"
    =REPLACEB("中国"; 1; 3; "?") Here the function returns the string "? " (note the space after the question mark), which is two bytes long. "? "
    =REPLACEB("中国"; 1; 4; "?") Here the function returns the string "?", which is one byte long. "?"
    =REPLACEB("中国"; 2; 0; "?") Here the function returns the string " ? 国" (note the spaces before and after the question mark), which is five bytes long. " ? 国"
    =REPLACEB("中国"; 2; 1; "?") Here the function returns the string " ?国" (note the space before the question mark), which is four bytes long. " ?国"
    =REPLACEB("中国"; 2; 2; "?") Here the function returns the string " ? " (note the spaces before and after the question mark), which is three bytes long. " ? "
    =REPLACEB("中国"; 2; 3; "?") Here the function returns the string " ?" (note the space before the question mark), which is two bytes long. " ?"

    Related LibreOffice functions:

    FINDB

    LEFTB

    LENB

    MIDB

    REPLACE

    RIGHTB

    SEARCHB

    ODF standard:

    Section 6.7.6, part 2

    Related (or similar) Excel functions:

    REPLACEB


    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Documentation/Calc_Functions/REPLACEB&oldid=707555"