TEXT Function (LibreOffice Calc)
The TEXT function in LibreOffice Calc converts numbers into formatted text strings using a specified number format code. It is essential for building readable labels, reports, dashboards, and structured output.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the TEXT Function Does ▾
- Converts numbers into formatted text
- Uses number format codes (same as cell formatting)
- Essential for labels, dashboards, and reports
- Works with dates, times, percentages, currency, and more
- Output is text, not a number
It is designed to be flexible, expressive, and formatting‑rich.
Syntax ▾
TEXT(value; format_code)
Arguments
-
value:
A number, date, time, or numeric expression. -
format_code:
A text string specifying the number format.
Common Format Code Examples ▾
| Format Code | Meaning | Example Output |
|---|---|---|
| “0” | Integer | 42 |
| “0.00” | Two decimals | 42.00 |
| “#,##0” | Thousands separator | 1,234 |
| “$0.00” | Currency | $42.00 |
| “0%” | Percentage | 42% |
| “0.00%” | Percentage w/ decimals | 42.50% |
| “YYYY-MM-DD” | Date | 2026-03-01 |
| “HH:MM” | Time | 14:30 |
| “0000” | Zero‑padded | 0042 |
Basic Examples ▾
Format a number with two decimals
=TEXT(3.14159; "0.00")
Returns "3.14".
Format a date
=TEXT(A1; "YYYY-MM-DD")
Format a time
=TEXT(A1; "HH:MM")
Format as currency
=TEXT(A1; "$0.00")
Format as percentage
=TEXT(A1; "0.00%")
Zero‑pad a number
=TEXT(A1; "00000")
Advanced Examples ▾
Build a label with formatted numbers
=CONCAT("Total: "; TEXT(A1; "$#,##0.00"))
Build a date label
=CONCAT("Report for "; TEXT(A1; "MMMM YYYY"))
Format a timestamp
=TEXT(A1; "YYYY-MM-DD HH:MM:SS")
Format a phone number
=TEXT(A1; "(000) 000-0000")
Format a part number
=TEXT(A1; "000-000-000")
Format with conditional text
=CONCAT(TEXT(A1; "0.00"); " units")
Format with thousands separator
=TEXT(A1; "#,##0")
Format negative numbers with parentheses
=TEXT(A1; "#,##0.00;(#,##0.00)")
Format dates with month names
=TEXT(A1; "DD MMMM YYYY")
Format time duration
=TEXT(A1; "[HH]:MM:SS")
Combine TEXT with TEXTJOIN
=TEXTJOIN(", "; TRUE; TEXT(A1:A5; "0.00"))
Edge Cases and Behavior Details ▾
TEXT always returns text
=TYPE(TEXT(42; "0")) → 2 (text)
TEXT does not change the underlying value
Only the display changes.
TEXT requires valid format codes
Invalid codes return Err:502.
TEXT converts numbers only
If value is text, TEXT returns text unchanged.
TEXT with dates/times requires numeric date/time values
=TEXT("2024-01-01"; "YYYY") → Err:502
TEXT does not localize automatically
Format codes must match locale settings.
TEXT does not accept ranges directly
Use array formulas or TEXTJOIN.
TEXT of an error propagates the error
=TEXT(#N/A; "0.00") → #N/A
Common Errors and Fixes ▾
Err:502 — Invalid argument
Occurs when:
- format_code is invalid
- value is non-numeric text
- format_code is not quoted
TEXT returns unexpected formatting
Cause:
- Locale mismatch
- Wrong format code
- Missing escape characters
TEXT returns
Cause:
- Cell too narrow
- Not enough space to display formatted text
Best Practices ▾
- Use TEXT to format numbers for labels and reports
- Use TEXTJOIN or CONCAT to assemble formatted strings
- Use TEXT for zero‑padding and structured codes
- Use TEXT for readable dates and times
- Use TEXT with conditional logic for dynamic formatting
- Validate format codes carefully
TEXT is your formatting engine — perfect for turning raw numbers into polished, human‑readable output for dashboards, reports, and structured labels.
Related Patterns and Alternatives ▾
- Use VALUE to convert formatted text back to numbers
- Use FIXED for formatted numbers without custom codes
- Use CONCAT, TEXTJOIN, CONCATENATE for assembly
- Use LEFT, RIGHT, MID for extraction
- Use SUBSTITUTE and REPLACE for editing
By mastering TEXT and its companion functions, you can build polished, readable, and highly structured text‑output workflows in LibreOffice Calc.