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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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
VALUE is your normalization engine — perfect for restoring numeric behavior after imports, formatting, or inconsistent data entry.
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.