Documentation/Calc Functions/TEXT
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
TEXT
Category:
Text
Summary:
Converts a number into text, in a specified format.
The TEXT function is particularly useful when you want to embed a formatted number inside a text string.
Syntax:
TEXT(Number; Format)
Returns:
Returns a text string, which is the converted value in the required format.
Arguments:
Value is the value to be processed, or a reference to a cell containing that value. The supplied value may be in any numeric format that is valid for your locale, for example, a number in decimal notation, a number in scientific notation, a number in fraction notation, a percentage, a currency, a date, a time, or a Boolean.
Format is a text string (in quotation marks), or a reference to a cell containing a text string, that specifies the format for the output text string. For more information about this argument, see the #Additional details: section below.
- If the Format string is not valid, TEXT reports an invalid argument error (Err:502).
Additional details:
To demonstrate the utility of the TEXT function, consider the following formula that was intended to show today's date:
="Today's date is " & TODAY()
Assuming that today is 2021-08-22, then this formula will display the string "Today's date is 44430", where 44430 is the date-time serial number for 2021-08-22. It is likely that this is not what the creator of the formula had in mind!
The following alternative formula uses the TEXT function and gives the output that was probably intended, with a properly formatted date:
="Today's date is " & TEXT(TODAY(); "yyyy-mm-dd")
This formula displays the string "Today's date is 2021-08-22".
Example format codes can be found on the Numbers tab of the Format Cells dialog, in the box labelled Format Code. Access this dialog using one of the following methods:
- Select ▸ on the Menu bar.
- Right-click on a spreadsheet cell and select Format Cells on the context menu.
- Press the Ctrl+1 keyboard shortcut.
As you make selections in the Category, Format, Language, and Options areas of the Numbers tab, Calc updates the contents of the Format Code box to reflect those selections.
For a more detailed description, search for "number format codes" in the help index or visit the relevant help page.
The behavior of the TEXT function is locale-dependent, and therefore it should not be regarded as portable.
Examples:
The following examples assume that your locale is set to English (USA).
| Formula | Description | Returns |
|---|---|---|
| =TEXT(12.34567; "###.##") | Here the supplied value is displayed with no leading zeroes, rounded to two decimal places. | 12.35 |
| =TEXT(D1; D2) where cell D1 contains the value 12 and cell D2 contains the string "000.00". | Here the supplied value is displayed with a leading zero, rounded to two decimal places. | 012.00 |
| =TEXT(123,456,789, "#,###") | Here the supplied value is displayed with the comma used as a digit group separator. | 123,456,789 |
| =TEXT(0.123456, "0.00%") | Here the supplied value is displayed as a percentage. | 12.35% |
| =TEXT(−6,789.4, "[$$-409]#,##0.00; -[$$-409]#,##0.00") | Here the supplied value is displayed as a USD currency value. Note that there are two parts to the format string separated by semicolons – positive values are displayed without a plus sign, while negative values are preceded by a minus sign. | -$6,789.40 |
| =TEXT(0, "YYYY-MM-DD HH:MM:SS") | Here the supplied value is displayed as a date-time, in the ISO8601 standard format. The resulting date-time is the default start point for Calc’s internal conversion from dates to numbers. | 1899-12-30 00:00:00 |
| =TEXT(123.456, "0.00E+#") | Here the supplied value is displayed in scientific notation. | 1.23E+2 |
| =TEXT(1.5, "# ?/?") | Here the supplied value is displayed in fraction notation. | 1 1/2 |
| =TEXT(1, "BOOLEAN") | Here the supplied value is displayed as a Boolean (a supplied value of 0 would return FALSE). | TRUE |
| =TEXT("xyz";"=== @ ===") | Here the supplied value is a string, which is displayed as part of a longer string. | === xyz === |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
TEXT