VALUE Function (LibreOffice Calc)
The VALUE function in LibreOffice Calc converts text that appears as a number, date, or time into a real numeric value. It is essential for cleaning imported data, restoring numeric behavior, and reversing TEXT-based formatting.
This guide provides syntax, examples, edge cases, and best practices, making it one of the most complete VALUE references available for LibreOffice Calc.
What the VALUE Function Does
- Converts text → number
- Recognizes numeric, date, and time formats
- Essential for cleaning imported or pasted data
- Restores numeric behavior (sorting, math, comparisons)
- Reverses TEXT‑based formatting
It is designed to be simple, reliable, and essential for data normalization.
Syntax
VALUE(text)
Arguments
- text:
A text string representing a number, date, or time.
Basic Examples
Convert a text number to a real number
=VALUE("42")
Returns 42.
Convert a decimal
=VALUE("3.14")
Convert a date string
=VALUE("2024-01-01")
Returns the date’s serial number.
Convert a time string
=VALUE("14:30")
Returns a fractional day value.
Convert a currency string
=VALUE("$1,234.56")
(Works if locale settings match.)
Advanced Examples
Convert text numbers imported from CSV
=VALUE(A1)
Convert numbers with spaces
=VALUE(SUBSTITUTE(A1; " "; ""))
Convert numbers with commas as thousand separators
=VALUE(SUBSTITUTE(A1; ","; ""))
Convert European decimal format
=VALUE(SUBSTITUTE(A1; ","; "."))
Convert a formatted label back to a number
=VALUE(TEXT(A1; "0.00"))
Convert a date label back to a date value
=VALUE(TEXT(A1; "YYYY-MM-DD"))
Convert a percentage string
=VALUE("42%")
Returns 0.42.
Convert a phone number (numeric only)
=VALUE(SUBSTITUTE(A1; "-"; ""))
Convert mixed text-number fields
=VALUE(MID(A1; FIND(": "; A1) + 2; 99))
Convert time duration
=VALUE("12:45:30")
Edge Cases and Behavior Details
VALUE requires text that looks like a number
=VALUE("ABC") → Err:502
VALUE respects locale settings
- Decimal separator
- Thousands separator
- Date format
- Time format
VALUE of an empty string returns 0
=VALUE("") → 0
VALUE of a blank cell returns 0
=VALUE(A1) → 0 if A1 is blank
VALUE of an error propagates the error
=VALUE(#N/A) → #N/A
VALUE ignores leading/trailing spaces
=VALUE(" 42 ") → 42
VALUE does not accept ranges
Use array formulas or apply VALUE to each cell.
VALUE cannot parse arbitrary text
=VALUE("Item 42") → Err:502
VALUE converts dates/times to serial numbers
=VALUE("2024-01-01") → 45292
=VALUE("12:00") → 0.5
Common Errors and Fixes
Err:502 — Invalid argument
Occurs when:
- text is not numeric
- text contains invalid characters
- text uses wrong locale formatting
Fix:
Normalize with SUBSTITUTE, TRIM, CLEAN.
VALUE returns wrong number
Cause:
- Locale mismatch
- Wrong decimal separator
- Thousands separator not removed
VALUE returns 0 unexpectedly
Cause:
- text = ""
- cell is blank
- formula returns ""
Best Practices
- Use VALUE to clean imported or pasted numeric data
- Use SUBSTITUTE to remove formatting characters
- Use TRIM and CLEAN before conversion
- Use VALUE to reverse TEXT formatting
- Use VALUE for date/time parsing when needed
- Validate locale formatting before conversion
Related Patterns and Alternatives
- Use TEXT to format numbers as text
- Use FIXED for formatted numbers without custom codes
- Use DATEVALUE and TIMEVALUE for explicit parsing
- Use INT, ROUND, TRUNC for numeric manipulation
- Use SUBSTITUTE, TRIM, CLEAN for preprocessing
By mastering VALUE and its companion functions, you can build clean, reliable, and fully numeric data‑processing workflows in LibreOffice Calc.
Reproducible example (tested in LibreOffice Calc)
This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.
=VALUE("25")
Expected result: 25.
Convert a numeric-looking string into a numeric value.
If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.