NUMBERVALUE Function (LibreOffice Calc)

Use NUMBERVALUE when a number arrives as text with decimal and thousands separators that do not match the locale of your spreadsheet. You tell Calc which characters are decimal and grouping separators; Calc produces a numeric value, ready for addition, sorting, or multiplication.

=NUMBERVALUE("1.234.567,89";",";".")

Result: 1234567.89 (numeric, not the text "1234567.89"). Here a comma marks decimals and periods group thousands. Calc removes the grouping periods and interprets the decimal comma. A displayed cell may show a different number of decimals or regional punctuation because cell formatting is separate from the numeric value. [LO-1]

The function appeared in LibreOffice 4.1 and is present in the official 26.8 Help. It is not a newly introduced 25.2 function. [LO-1]

Consider importing prices from a supplier whose exports use European punctuation:

Typing =NUMBERVALUE(A2;",";".") into B2 converts A2 regardless of how the punctuation would otherwise be interpreted by an en-US-style number parser. Fill the formula down and use B for arithmetic.

The related VALUE function accepts number-like text but expects locale-appropriate punctuation; it may also parse dates, times and currency according to the active locale. NUMBERVALUE is the better choice when the source’s separators are explicitly known. [LO-2]

Important: A numeric-looking imported value might already be stored as a number. Cell formatting cannot repair a text-import parsing mistake automatically. You can use =ISNUMBER(A2) and inspect the cell type before converting. Do not double-convert valid numbers without a reason.

=NUMBERVALUE(Text[; Decimal Separator[; Group Separator]])

The square brackets above indicate optional arguments; do not type the brackets. Text is required. Use semicolons as shown for Calc’s default English-language formula examples; a user can configure alternate formula argument delimiters (including commas) and localized function names under Tools → Options → LibreOffice Calc → Formula. Formula argument separators are not the same setting as punctuation embedded inside Text. [LO-1, LO-3]

According to Calc Help, you may omit the decimal argument when no decimal or group punctuation occurs in the input, and omit the group argument when no grouping punctuation occurs. The decimal character must not appear in the group-separator string. [LO-1]

For the input "1.234,50" the safe call is =NUMBERVALUE("1.234,50";",";"."). Writing =NUMBERVALUE("1.234,50") does not establish those separators and failed in the tested Calc environment.

ODF and compatibility names: The official desktop Calc function is spelled NUMBERVALUE; use the exact name the Help documents. Locale-specific display names and ODF interchange serialization should be checked on the target file/version when sharing workbooks; a successful on-screen English formula is not evidence of a universally identical ODF of:= encoding. [LO-1, LO-3]

The following formulas were entered into real Calc 25.2.3.2 and their numeric results compared with expected values. Numbers in the right column describe the underlying value, not guaranteed screen formatting.

The middle-dot and multi-character group examples are also described in official LibreOffice Help. [LO-1]

Worked cell example. Enter 2.500,27 as text in A2 (for a reproducible import, first format column A as Text). Enter =NUMBERVALUE(A2;",";".") in B2. The result is numeric 2500.27. Then enter =B2*4 in C2, obtaining 10001.08 by independent arithmetic. For an example that requires no manual cell setup, =NUMBERVALUE("2,5";",";".")*4 returns 10, verified natively. Actual formatting may add local symbols or trailing zeroes.

There are three different punctuation decisions to keep apart:

1. The semicolons in NUMBERVALUE(A2;",";".") delimit function arguments.
2. The quoted comma "," tells NUMBERVALUE the source string’s decimal mark.
3. The quoted period "." tells it the source string’s thousands/grouping mark.

Changing the formula delimiter preference in Calc does not turn "1.234,50" into "1,234.50". The quoted strings describe how the imported data is encoded; they remain characters even when Calc’s expression separator changes. Localized function names can also change formula text, without changing the required logic. [LO-3]

For copied examples, if your Calc installation uses commas between function arguments, use its configured syntax or switch to English names/default separator settings before pasting. Avoid automatic string substitutions across imported decimal commas; doing so can damage the data.

Sum a whole column of converted amounts

Suppose imported price strings are in A2:A5:

Copy B2 down to B5, then use =SUM(B2:B5) to obtain 3731.00 by independent arithmetic (2500.27 + 1234.50 − 7 + 3.23 = 3731.00). The individual conversion pattern and its behavior are natively tested; this exact four-row total is an independent calculation, not a claimed native assertion.

Because converting a bad text entry can yield an error, separately audit B2:B5 before trusting the grand total. A blanket IFERROR(...;0) may hide a price that failed to import.

Convert scientific notation and percentages

=NUMBERVALUE("123e12") returned 1.23×10^14 in Calc. Scientific notation needs no punctuation arguments if the source string has no decimal or grouping separator. [LO-1]

In the tested version, =NUMBERVALUE("3,5%";",";".") returned 0.035, because the percent sign divided the numeric part by 100. Repeated percent signs were also accepted: =NUMBERVALUE("4%%";".";",") returned 0.0004. These are native observations, not a promise that every locale, version, or export-import path handles percent syntax identically. When critical, check the intended unit and compare with a representative source value.

Nonbreaking spaces from copied reports

Some source systems insert nonbreaking spaces that look like ordinary spaces. The regular space U+0020, nonbreaking space U+00A0, and narrow no-break space U+202F are distinct characters.

These test formulas both returned 1234.5 in Calc 25.2.3.2, with the actual Unicode character appearing in both the number and the separator argument:

=NUMBERVALUE("1 234,50";",";" ")
=NUMBERVALUE("1 234,50";",";" ")

The first line uses U+00A0, the second U+202F. If an imported string contains a narrow no-break space but the formula specifies a normal space, the input may fail to parse. Inspect the code point before “fixing” a hidden-space problem. For mixed inputs, SUBSTITUTE can normalize a known character, but changing whitespace is a deliberate data-cleaning step and must be tested on real sample data.

Repeated or unusual grouping conventions

Calc accepted this input as 123456 in the tested environment:

=NUMBERVALUE("1,23,456";".";",")

Removing grouping characters is not validation that comma groups contain three digits. Likewise, Calc’s official examples accept the group token #!:

=NUMBERVALUE("1#!234#!567";"." ;"#!")

That formula returns 1234567 after removing #! markers. If you process financial records, validate expected grouping patterns at import time, instead of assuming a successful conversion proves correctly formatted source data.

These error labels and numeric codes were observed in LibreOffice Calc 25.2.3.2, not inferred from Excel’s error classifications.

Why a correct number appears with commas or trailing zeroes: conversion is numeric; the cell’s display format controls how it looks. Select an appropriate number format before concluding the computed value is wrong. [LO-1]

Why Excel imports behave differently: Excel’s NUMBERVALUE documentation explicitly describes empty-text and multi-character-separator rules that do not match the Calc observations above. A workbook can calculate differently after being opened in another spreadsheet application. [MS-1]

Do you need to remove currency symbols first? For "$1,000" with explicit separators, Calc returned #VALUE! in the tested environment. Use a known source schema and remove an expected prefix in a controlled preprocessing step, or use a locale-specific function if that is actually the intended interpretation. Do not apply SUBSTITUTE to remove every currency symbol in a mixed-currency dataset: you could silently combine CAD and USD amounts as if equal.

Why is a number rounded? Calc stores ordinary spreadsheet numbers as floating-point values; displayed decimal places need not equal internal precision. When handling long identifiers, account numbers or product codes, keep them as text to preserve leading zeroes and exact digits. NUMBERVALUE is for quantities and amounts, not identifiers.

A cross-application import strategy should either normalize numeric columns to a standard numeric representation before opening the file, or test a small sample in the target spreadsheet application. Passing Calc tests establishes Calc behavior only.

Does NUMBERVALUE change the original A2 text? No. The formula produces a numeric result in its own cell; A2 remains as imported unless you deliberately replace it.

Can I enter the example with commas between arguments? Only if your Calc formula separator settings allow it. In the documented Calc defaults, use semicolons; the quoted decimal and group characters are still whatever the source data requires. [LO-3]

Do I need both optional arguments? Not necessarily. =NUMBERVALUE("123·4";"·") is an official example using a decimal argument but no group argument. For plain "1234", =NUMBERVALUE("1234") worked natively. Explicitly specify both whenever the imported string contains both symbols. [LO-1]

Will two symbols always be accepted in the group argument? Calc Help explicitly permits a multi-character group string and demonstrates #!. The decimal separator is only one character. [LO-1]

Can it parse dates and currency? Do not depend on it for date/time/currency-locale parsing. Calc’s separate VALUE function describes those inputs and their locale requirements. [LO-2]

• VALUE — locale-aware text-to-number conversion, including certain date/time representations, where appropriate. [LO-2]
• SUBSTITUTE — replace a known character (for example a nonbreaking space) before number conversion.
• Official LibreOffice NUMBERVALUE Help — documented syntax, supported version, and examples. [LO-1]

Official pages checked again on 2026-10-13:

• [LO-1] The Document Foundation, NUMBERVALUE, LibreOffice 26.8 Help: https://help.libreoffice.org/latest/en-US/text/scalc/01/func_numbervalue.html — purpose, version since 4.1, argument order and separator constraints, official examples.
• [LO-2] The Document Foundation, VALUE, LibreOffice 26.8 Help: https://help.libreoffice.org/latest/en-US/text/scalc/01/func_value.html — locale dependence and date/time/currency distinctions.
• [LO-3] The Document Foundation, Formula Options, LibreOffice 26.8 Help: https://help.libreoffice.org/latest/en-US/text/shared/optionen/01060900.html — English/localized function names and configurable formula separators.
• [MS-1] Microsoft Support, NUMBERVALUE function: https://support.microsoft.com/en-us/excel/functions/numbervalue-function — official Excel syntax and documented differences; documentation checked, Excel not executed.
• [GS-1] Google Docs Editors Help, Google Sheets function list: https://support.google.com/docs/table/25273 — NUMBERVALUE absent from the enumerated list on access date. An absent entry does not itself prove impossibility in every environment.
• [GS-2] Google Docs Editors Help, VALUE: https://support.google.com/docs/answer/3094220 — separately supported Sheets function; documentation checked, Sheets not executed.

Native execution: 42 of 42 predefined expected/diagnostic cases matched in LibreOffice Calc 25.2.3.2 520(Build:2) on Linux via an isolated headless UNO session. 32 numeric assertions matched the saved expected values; the later review independently modeled all 32 conversions using Decimal arithmetic. 10 error diagnostics were compared against separately recorded preliminary native observations, not a normative external error specification. The saved ODS workbook, input JSON, Python UNO test runner and full observed results permit reproduction. The worked four-row total is an explicitly labeled independent calculation; Excel and Sheets examples were documentation-checked, not executed.