Text Functions
This section contains descriptions of the Text functions.
Using double quotation marks in formulas
To include a text string in a formula, place the text string between two double quotation marks (") and Calc takes the characters in the string without attempting to interpret them. For example, the formula ="Hello world!" displays the text string Hello world! in the cell, with no surrounding double quotation marks.
The more complex formula =CONCATENATE("Life is really simple, "; "but we insist on making it complicated "; "(Confucius).") concatenates three individual strings in double quotation marks, outputting Life is really simple, but we insist on making it complicated (Confucius).
To place a literal double quotation mark within a string inside a formula, two methods can be used:
-
You can "escape" the double quotation mark with an additional double quotation mark, and Calc treats the escaped double quotation mark as a literal value. For example, the formula ="My name is ""John Doe""." outputs the string My name is "John Doe". Another simple example is the formula =UNICODE("""") which returns 34, the decimal value of the Unicode quotation mark character (U+0022) — here the first and fourth double quotation marks indicate the beginning and end of the string, while the second double quotation mark escapes the third.
-
You can use the CHAR function or the UNICHAR function to insert a double quotation mark. For example, the formula =UNICHAR(34) & "The Catcher in the Rye" & UNICHAR(34) & " is a famous book by J. D. Salinger." displays the string "The Catcher in the Rye" is a famous book by J. D. Salinger.
Be aware that Calc's AutoCorrect function may modify double quotation marks. AutoCorrect should not change the double quotation marks within formula cells but may change those used in non-formula cells containing text. For example, if you copy a string that is surrounded by some other form of typographical double quotation marks, such as the left double quotation mark (U+201C) and the right double quotation mark (U+201D), and then paste into a formula cell, an error may result. Open the Double Quotes area of the dialog to set the characters used to automatically correct the start and end typographical double quotation marks. Uncheck the toggle button to disable the feature.
BAHTTEXT
將數字轉換為泰語文字，包含泰幣名稱。
BAHTTEXT(Number)
Number 可以是任何數字。「Baht」會附加到數字的整數部分，而「Satang」會附加到數字的小數部分。
=BAHTTEXT(12.65) 傳回使用泰文字元的字串，表示「12 銖 65 撒丹」。
COM.MICROSOFT.BAHTTEXT
BASE
Converts a positive integer to a specified base into a text from the numbering system. The digits 0-9 and the letters A-Z are used.
BASE(Number; Radix [; MinimumLength])
Number 是指要轉換的正整數。
Radix indicates the base of the numeral system. It may be any positive integer between 2 and 36.
MinimumLength (選擇性) 會決定已建立之字元順序的最小長度。若文字比指定的最小長度短，則會在字串左側加上零。
=BASE(17;10;4) 會以十進制系統傳回 0017。
=BASE(17;2) 會以二進制系統傳回 10001。
=BASE(255;16;4) 會以十六進制系統傳回 00FF。
CHAR
根據目前的代碼表格將數字轉換為字元。數字可以是兩位數或三位數的整數。
CHAR(Number)
Number 是指介於 1 與 255 之間的數字，表示字元的代碼值。
=CHAR(100) 傳回字元 d。
="abc" & CHAR(10) & "def" 會將斷行字元插入字串中。
CLEAN
所有非列印字元從字串中移除。
CLEAN("Text")
Text 是指要移除所有不可列印的字元之文字。
=LEN(CLEAN(CHAR(7) & "LibreOffice Calc" & CHAR(8))) returns 16, showing that the CLEAN function removes the non-printable Unicode U+0007 ("BEL") and U+0008 ("BS") characters at the beginning and end of the string argument. CLEAN does not remove spaces.
CODE
傳回文字字串中第一個字元的數字碼。
CODE("Text")
Text 是要尋找其第一個字元之代碼的文字。
=CODE("Hieronymus") 傳回 72，=CODE("hieroglyphic") 傳回 104。
此處所使用的編碼並非 ASCII 碼，而是目前編碼表中的編碼。
CONCATENATE
將數個文字字串合併為一個字串。
CONCATENATE(String 1 [; String 2 [; … [; String 255]]])
String 1[; String 2][; … ;[String 255]] are strings or references to cells containing strings.
=CONCATENATE("Good ";"Morning ";"Mrs. ";"Doe") 傳回：Good Morning Mrs. Doe。
DECIMAL
Converts text that represents a number in a numeral system with the given base radix to a positive integer. The radix must be in the range 2 to 36. Spaces and tabs are ignored. The Text field is not case-sensitive.
If the radix is 16, a leading x or X or 0x or 0X, and an appended h or H, are disregarded. If the radix is 2, an appended b or B is disregarded. Other characters that do not belong to the numeral system generate an error.
DECIMAL("Text"; Radix)
Text is the text to be converted.
Radix indicates the base of the numeral system. It may be any positive integer between 2 and 36.
=DECIMAL("17";10) 傳回 17。
=DECIMAL("FACE";16) 傳回 64206。
=DECIMAL("0101";2) 傳回 5。
DOLLAR
Converts a number to a string representing the amount in the currency format, rounded to a specified decimal places, using the decimal separator that corresponds to the current locale setting. In the Value field enter the number to be converted. Optionally, you may enter the number of decimal places in the Decimals field. If no value is specified, all numbers in currency format will be displayed with two decimal places.
您在系統設定中設定貨幣格式。
DOLLAR(Value [; Decimals])
Value 是數字、包含數字的儲存格參照，或傳回數字的公式。
Decimals 是選用的小數點位數。
=DOLLAR(255) returns $255.00 for the English (USA) locale and USD (dollar) currency; ￥255.00 for the Japanese locale and JPY (yen) currency; or 255,00 € for the German (Germany) locale and EUR (euro) currency.
=DOLLAR(367.456;2) returns $367.46.
EXACT
比較兩個字串，如果都相同則會傳回 TRUE。 此函式會區分大小寫。
EXACT("Text1"; "Text2")
Text1 是指要比較的第一段文字。
Text2 是指要比較的第二段文字。
=EXACT("microsystems";"Microsystems") 傳回 FALSE。
FIND
Returns the position of a string of text within another string.You can also define where to begin the search. The search term can be a number or any string of characters. The search is case-sensitive.
FIND("FindText"; "Text" [; Position])
FindText 是指要尋找的文字。
Text 是指進行搜尋所在的文字。
Position (選擇性) 是指文字中開始搜尋的位置。
=FIND(76;998877665544) 傳回 6。
FIXED
Returns a number as text with a specified number of decimal places and optional thousands separators.
FIXED(Number; [Decimals = 2 [; NoThousandsSeparators = FALSE]])
Number is rounded to Decimals places (after the decimal separator) and the result formatted as text, using locale-specific settings.
Decimals (optional) refers to the number of decimal places to be displayed. If Decimals is negative, Number is rounded to ABS(Decimals) places to the left from the decimal point. If Decimals is a fraction, it is truncated actually ignoring what is the closest integer.
NoThousandsSeparators (optional) determines whether the thousands separator is used. If it is TRUE or non-zero, then group separators are omitted from the resulting string. If the parameter is equal to 0 or if it is missing altogether, the thousands separators of your current locale setting are displayed.
=FIXED(1234567.89;3) 傳回 1,234,567.890 作為文字字串。
=FIXED(123456.789;;TRUE) returns 123456.79 as a text string.
=FIXED(12345.6789;-2) returns 12,300 as a text string.
=FIXED(12134567.89;-3;1) returns 12135000 as a text string.
=FIXED(12345.789;3/4) returns 12,346 as a text string.
=FIXED(12345.789;8/5) returns 12,345.8 as a text string.
LEFT
傳回文字中的第一個或前幾個字元。
LEFT("Text" [; Number])
Text 是要傳回初始部份字串的文字。
Number (選擇性) 指定起始文字的字元數。若未定義此參數，則傳回一個字元。
=LEFT("output";3) 傳回「out」。
LEFTB
傳回雙位元字元集文字的第一個字元。
LEFTB("Text" [; Number_bytes])
Text 是要傳回初始部份字串的文字。
Number (選擇性) 指定要 LEFTB 擷取的字元數，以位元為單位。若未定義此參數，則傳回一個字元。
=LEFTB("中国";1) returns " " (1 byte is only half a DBCS character and a space character is returned instead).
=LEFTB("中国";2) returns "中" (2 bytes constitute one complete DBCS character).
=LEFTB("中国";3) returns "中 " (3 bytes constitute one DBCS character and a half; the last character returned is therefore a space character).
=LEFTB("中国";4) returns "中国" (4 bytes constitute two complete DBCS characters).
=LEFTB("office";3) returns "off" (3 non-DBCS characters each consisting of 1 byte).
LEN
傳回包含空格在內的字串長度。
LEN("Text")
Text 是要測定長度的文字。
=LEN("Good Afternoon") 傳回 14。
=LEN(12345.67) 傳回 8。
LENB
在雙位元字元集的語言，傳回用來表達文字字串內的字元的位元數。
LEN("Text")
Text 是要測定長度的文字。
LENB("中") 傳回 2 (一個雙位元字元集字元由兩位元構成)。
LENB("台灣") 傳回 4 (兩個雙位元字元集字元，各由兩位元構成)。
LENB("office") 傳回 6 (六個非雙位元字元集字元，每個一位元)。
=LENB("Good Afternoon") 傳回 14。
=LENB(12345.67) 傳回 8。
LOWER
將文字字串中所有的大寫字母轉換成小寫字母。
LOWER("Text")
Text 是指要轉換的文字。
=LOWER("Sun") 傳回 sun。
MID
傳回文字內的文字字串。參數可指定字元的起始位置和數量。
MID("Text"; Start; Number)
Text 是含有要擷取字元的文字。
Start 是要擷取的第一個字元在 Text 中的位置。
Number 指定擷取文字的字元數。
=MID("office";2;2) 傳回 ff。
MIDB
傳回雙位元組字元集文字內的文字字串。參數指定字元的起始位置和數量。
MIDB("Text"; Start; Number_bytes)
Text 是含有要擷取字元的文字。
Start 是要擷取的第一個字元在 Text 中的位置。
Number_bytes 指定 MIDB 從 Text 傳回的文字字元數量 (以位元為單位)。
=MIDB("中国";1;0) returns "" (0 bytes is always an empty string).
=MIDB("中国";1;1) returns " " (1 byte is only half a DBCS character and therefore the result is a space character).
=MIDB("中国";1;2) returns "中" (2 bytes constitute one complete DBCS character).
=MIDB("中国";1;3) returns "中 " (3 bytes constitute one and a half DBCS character; the last byte results in a space character).
=MIDB("中国";1;4) returns "中国" (4 bytes constitute two complete DBCS characters).
=MIDB("中国";2;1) returns " " (byte position 2 is not at the beginning of a character in a DBCS string; 1 space character is returned).
=MIDB("中国";2;2) returns " " (byte position 2 points to the last half of the first character in the DBCS string; the 2 bytes asked for therefore constitutes the last half of the first character and the first half of the second character in the string; 2 space characters are therefore returned).
=MIDB("中国";2;3) returns " 国" (byte position 2 is not at the beginning of a character in a DBCS string; a space character is returned for byte position 2).
=MIDB("中国";3;1) returns " " (byte position 3 is at the beginning of a character in a DBCS string, but 1 byte is only half a DBCS character and a space character is therefore returned instead).
=MIDB("中国";3;2) returns "国" (byte position 3 is at the beginning of a character in a DBCS string, and 2 bytes constitute one DBCS character).
=MIDB("office";2;3) returns "ffi" (byte position 2 is at the beginning of a character in a non-DBCS string, and 3 bytes of a non-DBCS string constitute 3 characters).
PROPER
將文字字串中所有單詞的首字母轉換成大寫字母。
PROPER("Text")
Text 是指要轉換的文字。
=PROPER("the document foundation") returns The Document Foundation.
REPLACE
用不同的文字字串取代部分文字字串。此函式可用於取代字元與數字 (自動轉換為文字)。函式的結果一律顯示為文字。如果您要用已被文字取代的數字執行進一步計算，則必須使用 VALUE 函式將其轉換回數字。
對於包含數字的文字，如果您不希望它被解譯為數字並自動轉換成文字，請在該文字前後加上引號。
REPLACE("Text"; Position; Length; "NewText")
Text 是指部份內容將遭取代的文字。
Position 是指文字內開始取代的位置。
Length 是指在 Text 中要取代的字元數。
NewText 是指取代 Text 的文字。
=REPLACE("1234567";1;1;"444") 傳回「444234567」。位置 1 的一個字元會由完整的 NewText 取代。
REPT
依指定的複製份數，重複字元字串。
REPT("Text"; Number)
Text 是指要重複的文字。
Number 是指重複的次數。
=REPT("Good morning";2) 傳回 Good morningGood morning。
Refer to the REPT wiki page for more details about this function.
RIGHT
傳回文字中的最後一個或後幾個字元。
RIGHT("Text" [; Number])
Text 是指要傳回右側部分字串的文字。
Number (optional) is the number of characters from the right part of the text. If this parameter is not defined, one character is returned.
=RIGHT("Sun";2) 傳回 un。
RIGHTB
傳回雙位元字元集 (DBCS) 文字的最後幾個字元。
RIGHTB("Text" [; Number_bytes])
Text 是指要傳回右側部分字串的文字。
Number_bytes (optional) specifies the number of characters you want RIGHTB to extract, based on bytes. If this parameter is not defined, one byte is returned.
RIGHTB("台灣";1) returns " " (一位元只是一個雙位元字元集字元的一半，因此只傳回一個空白字元)。
RIGHTB("台灣";2) returns "灣" (兩位元構成一個完整的雙位元組字元集的字元)。
RIGHTB("台灣";3) returns " 灣" (三位元構成半個加一個雙位元組字元集的字元；因此針對那半個傳回空白字元)。
RIGHTB("台灣";4) 傳回 "台灣" (四位元構成兩個完整的雙位元組字元集的字元)。
RIGHTB("office";3) 傳回 "ice" (三個非雙位元字元集字元，每個一位元)。
SEARCH
Returns the position of a text segment within a character string. You can set the start of the search as an option. The search text can be a number or any sequence of characters. The search is not case-sensitive. If the text is not found, returns error 519 (#VALUE).
The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every regular expression metacharacter or operator with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in .
When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".
SEARCH("FindText"; "Text" [; Position])
FindText 是指要搜尋的文字。
Text 是指將進行搜尋的文字。
Position (選擇性) 是指文字中開始搜尋的位置。
=SEARCH(54;998877665544) 傳回 10。
SUBSTITUTE
用新文字代替字串中的舊文字。
SUBSTITUTE("Text"; "SearchText"; "NewText" [; Occurrence])
Text 是指要替換其中文字片段的文字。
SearchText 是指要取代 (多次) 的文字區段。
NewText 是用以取代文字區段的文字。
Occurrence (選擇性) 是指要取代的搜尋文字發生數。若缺少此參數，則會取代所有搜尋文字。
=SUBSTITUTE("123123123";"3";"abc") 傳回 12abc12abc12abc。
=SUBSTITUTE("123123123";"3";"abc";2) 傳回 12312abc123。
T
此函式傳回目標文字，目標並非文字時傳回空白的文字字串。
T(Value)
若 Value 是指文字字串或參照文字字串，T 會傳回該文字字串；否則，T 會傳回空白的文字字串。
=T(12345) 傳回空字串。
=T("12345") 傳回字串 12345。
TEXT
Converts a value into text according to a given format.
TEXT(Value; Format)
Value is the value (numerical or textual) to be converted.
Format 是指定義格式的文字。請依儲存格格式中設定的語言，使用小數和千位分隔符。
=TEXT(12.34567;"###.##") 傳回文字 12.35。
=TEXT(12.34567;"000.00") 傳回文字 012.35。
=TEXT("xyz";"=== @ ===") returns the text === xyz ===
See also Number format codes: custom format codes defined by the user.
TRIM
移除字串的空格，將字詞與字詞之間的只留一個空格。
TRIM("Text")
Text refers to text in which spaces are to be removed.
=TRIM(" hello world ") returns hello world without leading and trailing spaces and with single space between words.
UNICHAR
將編碼轉換為 Unicode 字元或字母。
UNICHAR(number)
=UNICHAR(169) 傳回版權字元 ©。
See also the UNICODE() function.
UNICODE
傳回文字字串中第一個 Unicode 字元的數字碼。
UNICODE("Text")
=UNICODE("©") 為版權字元傳回 Unicode 數字 169。
See also the UNICHAR() function.
UPPER
將 [文字] 欄位中指定的字串轉換為大寫字母。
UPPER("Text")
Text 表示要轉換為大寫字母的小寫字母。
=UPPER("Good Morning") 傳回 GOOD MORNING。