Documentation/Calc Functions/SEARCHB
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
SEARCHB
Category:
Text
Summary:
Uses a case-insensitive 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:
SEARCHB(FindText; 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, SEARCHB uses the value 1.
- If Position is a non-integer value, then SEARCHB truncates it to an integer value.
- If Position is less than 1, then SEARCHB reports an invalid argument error (Err:502).
- After truncation, if Position is greater than the length of the string to be searched, then SEARCHB reports a #VALUE! error.
- If no match is found, then SEARCHB 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 SEARCHB function
SEARCHB does not support wildcards or regular expressions.
Use the FINDB function if you need searches that are case-sensitive.
In ▸ ▸ ▸ (or ▸ ▸ ▸ on macOS), the setting for Search criteria = and <> must apply to whole cells has no effect on the behavior of SEARCHB.
Suggestions for handling the #VALUE! error that is returned when no match is found include:
=IF(ISERROR(SEARCHB ("xyz"; "abcdef"; 1)); "ERR: Missing Substring"; "Substring Present")returns "ERR: Missing Substring" and does not propagate the error from the SEARCHB function.=IF(ISNUMBER(SEARCHB ("xyz"; "abcdef"; 1)); "Substring Present"; "ERR: Missing Substring")returns "ERR: Missing Substring" and does not propagate the error from the SEARCHB function.
General information about byte-position functions
 |
Note:
|
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 demonstrate the behavior of SEARCHB for strings comprising only single-byte characters.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rook | Knight | 1 |
| Formula | Description | Returns |
|---|---|---|
| =SEARCHB("fifty"; "Fifty-fifty") | The match is not case-sensitive. | 1 |
| =SEARCHB(76; 998,877,667,654) | By default, matching starts at the first byte of the string to be searched. | 6 |
| =SEARCHB(76; 998,877,667,654; 7) | The matching starts at the seventh byte of the string to be searched. | 9 |
| =SEARCHB(B1; C1; D1) | There is no match. | #VALUE! |
The remaining examples demonstrate the behavior of SEARCHB 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 |
|---|---|---|
| =SEARCHB("a"; "ＬｉｂｒｅＯｆｆｉｃｅ Ｃａｌｃ") |
The FindText 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! |
| =SEARCHB("ａ"; "ＬｉｂｒｅＯｆｆｉｃｅ Ｃａｌｃ") | First byte of the double-byte "ａ" character within the Text argument. | 27 |
| =SEARCHB("ａ"; "LibreOffice Ｃａｌｃ") |
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:
ODF standard:
Related (or similar) Excel functions:
SEARCHB