Documentation/Calc Functions/FINDB

    From The Document Foundation Wiki

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

    Other languages:


    Function name:

    FINDB

    Category:

    Text

    Summary:

    Uses a case-sensitive match to find the start byte of one text string within another text string. Each string may contain both single-byte and double-byte characters.

    Syntax:

    FINDB(Find Text ; Text [; Position])

    Returns:

    Returns a positive integer which is the position of the first byte of the first occurrence of the string to be found, within the string to be searched. The value returned cannot be greater than the length (in bytes) of the string to be searched.

    Arguments:

    Find 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 to be found.

    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 to be searched.

    Position is a positive integer, or a reference to a cell containing a positive integer, that is the byte position from which the search starts. If Position is omitted, FINDB uses the value 1.

    • If Position is a non-integer value, FINDB truncates it to an integer value.
    • After truncation, if Position is less than 1 or greater than (LENB(Text) - LENB(Find Text) + 1), then FINDB reports an invalid argument error (Err:502).
    • If no match is found, then FINDB reports a #VALUE! error. This is an error condition, which must be handled if used as an argument to another function.

    Additional details:

    Details specific to FINDB function

    FINDB does not support wildcards or regular expressions.

    Use the SEARCHB function if you need searches that are not case sensitive.

    In Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS), the setting for Search criteria = and <> must apply to whole cells has no effect on the behavior of FINDB.

    Suggestions for handling the #VALUE! error that is returned when no match is found include:

    • =IF(ISERROR(FINDB("xyz"; "abcdef"; 1)); "ERR: Missing Substring"; "Substring Present") returns "ERR: Missing Substring" and does not propagate the error from the FINDB function.
    • =IF(ISNUMBER(FINDB("xyz"; "abcdef"; 1)); "Substring Present"; "ERR: Missing Substring") returns "ERR: Missing Substring" and does not propagate the error from the FINDB 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:

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

    Formula Description Returns
    =FINDB("fifty"; "Fifty-fifty") Here the function returns 7 because the match is case-sensitive. 7
    =FINDB(76; 998877667654) Here the function returns 6 because by default matching starts at the first byte of the string to be searched. 6
    =FINDB(76; 998877667654; 7) Here the function returns 9 because matching starts at the seventh byte of the string to be searched. 9
    =FINDB(D1; D2; D3) where cell D1, D2, and D3 contain "Rook", "Knight" and the number 1 respectively. Here the function returns the #VALUE! error because there is no match. #VALUE!

    The remaining examples demonstrate the behavior of FINDB for strings that involve double-byte characters. For ease of understanding, these examples avoid using text in languages such as Chinese, Japanese, and Korean, that utilize double-byte characters. Instead, as this is an English language page, these examples reference double-byte characters in the Halfwidth and Fullwidth Forms Unicode block that make sense in English. The Unicode character U+3000, known as the Ideographic Space, is also used in the first two of these examples.

    Formula Description Returns
    =FINDB("a"; "ＬｉｂｒｅＯｆｆｉｃｅ　Ｃａｌｃ") Here the Find Text argument is a single-byte character (U+0061) whereas the Text argument comprises only double-byte characters. In particular, the "a" within the Text argument is the character U+FF41 ("Fullwidth Latin Small Letter A"), which is different to U+0061 ("Latin Small Letter A"). The function returns the #VALUE! error because there is no match. #VALUE!
    =FINDB("ａ"; "ＬｉｂｒｅＯｆｆｉｃｅ　Ｃａｌｃ") Here the function returns 27, the first byte of the double-byte "ａ" character within the Text argument. 27
    =FINDB("ａ"; "LibreOffice Ｃａｌｃ") Here the Text argument contains 12 single-byte characters followed by four double-byte characters. The function returns 15, the first byte of the double-byte "ａ" character within the Text argument. 15

    Related LibreOffice functions:

    FIND

    LEFTB

    LENB

    MIDB

    REPLACEB

    RIGHTB

    SEARCHB

    ODF standard:

    Section 6.7.2, part 2

    Related (or similar) Excel functions:

    FINDB


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