Excel 2013 or laterExamples need Excel 2021 or laterText

NUMBERVALUE

NUMERIEKE.WAARDE in Dutch Excel

Turns text into a number using the decimal and thousands separators you name, whatever the regional setting of the computer.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1Invoice no.SupplierAmount (text)Amount
2I-2601Elektro Groothandel Zuid12.450,7512,450.7512.450,75=NUMBERVALUE(C2, ",", ".")=NUMBERVALUE(C2; ","; ".")
3I-2602Kabelcentrale Rijnmond980,00
4I-2603Bouwstoffen Hoogvliet7.205,10
5I-2604Gereedschap Direct15,50
6I-2605Koperhandel Mulder101.300,00
Note: The amounts are text in both language settings: a period groups thousands and a comma marks the decimals, as the Dutch system wrote them.

Result: 12,450.7512.450,75

=NUMBERVALUE(C2, ",", ".")
=NUMBERVALUE(C2; ","; ".")

What NUMBERVALUE does and when to use it

What it does: it turns text such as 1.234,56 into a number, using the decimal and thousands separators you name.

When to use it: when a file written for another regional setting than yours arrives as text, such as a bank file, a subsidiary ledger or a supplier portal. Unlike VALUE, it does not follow the setting of the computer it runs on. For a column you import every month, Power Query is better; for one single column, Text to Columns is quicker.

Two main gains:

  • Same answer everywhere: the separators are in the formula, so the result does not depend on the regional setting of whoever opens the workbook.
  • Stays live: unlike Text to Columns or Find and Replace, the number updates when the source text changes.

How Excel evaluates it

Excel first removes every space from the text, also those between digits. It then looks at the end for percent signs and remembers how many there are. It reads the rest from left to right:

  • an optional sign in front
  • digits
  • the group separator wherever you put it before the decimal mark (these are dropped)
  • the decimal separator, once

The digits become the number, and each percent sign divides it by 100. Empty text returns 0.

If anything is left over that does not fit, such as a letter, a currency symbol or a minus sign at the end, the result is #VALUE!.

Watch out: Leave out the separators and the result depends on who opens the workbook: on English settings "12,5 %" gives 1.25 (the comma is dropped as a thousands mark), and no error warns you.

Mental model

A translator with a glossary: you tell it which mark means "decimal" and which means "thousands", and it reads the text with your glossary instead of the one built into the computer.

Terms used on this page: workbook

Syntax

Official NUMBERVALUE documentation
NUMBERVALUE(text, [decimal_separator], [group_separator])
NUMBERVALUE(text; [decimal_separator]; [group_separator])
ArgumentTypeMeaningDefault
textRequiredThe text to turn into a number.
decimal_separatorOptionalThe character used as the decimal mark in the text.Decimal separator of the regional setting
group_separatorOptionalThe character used to group thousands in the text.Group separator of the regional setting

Return value:A number: the text read with the separators you specify

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Read purchase invoice amounts written with decimal commas

The purchase ledger of Veldhuis Installatie BV was exported to CSV by a Dutch system. You open it on a laptop with English settings, and the amounts such as 12.450,75 arrive as text. You need the amount in C2 as a real number to start adding up.

ABCDE
1Invoice no.SupplierAmount (text)Amount
2I-2601Elektro Groothandel Zuid12.450,7512,450.7512.450,75=NUMBERVALUE(C2, ",", ".")=NUMBERVALUE(C2; ","; ".")
3I-2602Kabelcentrale Rijnmond980,00
4I-2603Bouwstoffen Hoogvliet7.205,10
5I-2604Gereedschap Direct15,50
6I-2605Koperhandel Mulder101.300,00
7
Formula cell (D2)InputNote: The amounts are text in both language settings: a period groups thousands and a comma marks the decimals, as the Dutch system wrote them.

Formula in D2

=NUMBERVALUE(C2, ",", ".")
=NUMBERVALUE(C2; ","; ".")

Result: 12,450.7512.450,75

Step by step
  1. Excel takes the text "12.450,75" from C2 as the first argument.
  2. The second argument says that "," is the decimal mark, so the part after the comma, 75, will be the decimals.
  3. The third argument says that "." groups thousands. It stands before the decimal mark, so Excel drops it: 12450,75.
  4. The digits 12450 and the decimals 75 give 12,450.7512.450,75. The result is a real number, right-aligned, and SUM will include it.
  5. Copied down, D3 reads "980,00" and returns 980; D6 reads "101.300,00" and returns 101,300101.300.

Watch out: Swap the two separators by accident, NUMBERVALUE(C2, ".", ","), and "12.450,75" returns #VALUE!, because a group separator after the decimal mark is not allowed. A swapped pair on a value without thousands, such as "15,50", does not fail: it returns 1550.

More on this example: why it works

Why it works

The formula carries its own reading rules: the file's decimal comma and thousands period are written in the arguments, so it does not matter whether the laptop thinks in Dutch or English. VALUE would have used the laptop's rules and returned #VALUE! or a wrong number.

Intermediate

Apply depreciation rates that arrive as text with a percent sign

The fixed asset register of Veldhuis Installatie BV was typed into a text field, so the rates read "12,5 %", "20%" and "33,33 %", some with a space before the percent sign. You recalculate the annual depreciation as cost times rate. The workbook is reviewed on computers with both Dutch and English settings.

ABCDE
1AssetCostRate (text)Annual depreciation
2Service van€48,000.00€ 48.000,0012,5 %€6,000.00€ 6.000,00=B2 * NUMBERVALUE(C2, ",", ".")=B2 * NUMBERVALUE(C2; ","; ".")
3Power tools€9,600.00€ 9.600,0020%
4Laptops€5,400.00€ 5.400,0033,33 %
5Office furniture€15,000.00€ 15.000,0010 %
6
Formula cell (D2)Input

Formula in D2

=B2 * NUMBERVALUE(C2, ",", ".")
=B2 * NUMBERVALUE(C2; ","; ".")

Result: €6,000.00€ 6.000,00

Step by step
  1. Excel evaluates NUMBERVALUE(C2, ",", ".") first. C2 holds "12,5 %".
  2. All spaces are removed, leaving "12,5%". The percent sign at the end is noted: divide by 100 once.
  3. With the comma as decimal mark, 12,5 is read as 12.5. Then 12.5 / 100 = 0.125.
  4. Excel multiplies B2 by that number: 48,00048.000 × 0.125 = 6,0006.000.
  5. In D3 the text "20%" gives 0.2 and 9,6009.600 × 0.2 = 1,9201.920. In D4 "33,33 %" gives 0.3333 and 5,4005.400 × 0.3333 = 1,799.821.799,82.

Watch out: Leave out the separators on a computer with English settings and "12,5 %" is read as 125 %, so D2 becomes 48,00048.000 × 1.25 = 60,00060.000. Nothing turns red; only the 10 times larger depreciation gives it away. Compare each result with the cost to catch this.

More on this example: why it works

Why it works

NUMBERVALUE ignores spaces and understands the percent sign by itself, so one formula handles all four spellings. The explicit decimal comma matters: it keeps 12,5 from being read as 125 on a computer with English settings.

Advanced

Read amounts from two portals with different separators

For the cut-off test you collect open purchase invoices from two supplier portals. Portal A writes 1.234,56 and portal B writes 2,450.002.450,00. Column B records which decimal mark each line uses. You want one formula for the whole column that reads each amount by its own rule, so that the lines can be summed together.

ABCDE
1PortalDecimal markAmount (text)Amount
2B.12,300.7512,300.7512.300,75=NUMBERVALUE(C2, B2, IF(B2 = ",", ".", ","))=NUMBERVALUE(C2; B2; IF(B2 = ","; "."; ","))
3A,1.234,56
4A,980,5
5B.2,450.00
6A,15.000
7
Formula cell (D2)InputNote: Enter the cells in column B and C as text (format the columns as Text first), otherwise Excel converts them while you type.

Formula in D2

=NUMBERVALUE(C2, B2, IF(B2 = ",", ".", ","))
=NUMBERVALUE(C2; B2; IF(B2 = ","; "."; ","))

Result: 12,300.7512.300,75

Step by step
  1. Excel evaluates the inner IF first for D2. B2 holds ".", so the test B2 = "," is FALSE and the IF returns its third argument: ",".
  2. NUMBERVALUE now has three arguments: the text "12,300.7512.300,75" from C2, the decimal separator "." taken from B2 and the group separator "," from the IF.
  3. The comma sits before the period, so it is dropped as a thousands mark: 12300.75. The result is 12,300.7512.300,75.
  4. In D3 the mark is ",", so the IF returns "." and "1.234,56" becomes 1,234.561.234,56. In D4 "980,5" has no thousands mark and becomes 980.5.
  5. In D6 the text is "15.000" with mark ",": the period is a thousands mark, so the result is 15,00015.000, not 15.

Watch out: A mistake in column B is not an error but a wrong number. If a portal A line is labeled ".", then "15.000" returns 15 instead of 15,00015.000, and the total drops by 14,98514.985 without a warning. Test the labels against a few known lines.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Both separator arguments can be cell references or formulas, not only typed characters. Column B holds the one fact that differs per source, and the IF derives the opposite character, so a new portal needs one cell, not a new formula.

More to watch out for

  • A decimal mark that appears twice, such as "1.234.567" read with "." as decimal mark, returns #VALUE!. That is one of the few cases where the function protects you.

Variations

=NUMBERVALUE(C2, ",", ".")
=NUMBERVALUE(C2; ","; ".")

Use this when the whole file comes from one source with a fixed format; no label column is needed.

MasterExcel 2021 or later

Control total of a bank export with trailing minus signs

Veldhuis Installatie BV gave you the December bank export as text: Dutch separators, a minus sign after the amount for debits, and a stray space or two. You want the net movement of the six lines in one cell, to agree with the difference between the opening and closing balance on the bank statement. Next month a new file follows with the same layout.

ABCDE
1DateDescriptionAmount (text)Net movement
22 Dec 2025Customer receipt1.250,009,533.859.533,85=LET( txt, TRIM(C2:C7), isNeg, RIGHT(txt, 1) = "-", body, IF(isNeg, LEFT(txt, LEN(txt) - 1), txt), amount, NUMBERVALUE(body, ",", "."), SUM(IF(isNeg, -amount, amount)) )=LET( txt; TRIM(C2:C7); isNeg; RIGHT(txt; 1) = "-"; body; IF(isNeg; LEFT(txt; LEN(txt) - 1); txt); amount; NUMBERVALUE(body; ","; "."); SUM(IF(isNeg; -amount; amount)) )
35 Dec 2025Subcontractor payment3.480,50-
49 Dec 2025Customer receipt720,00
515 Dec 2025Insurance premium95,25-
619 Dec 2025Customer receipt12.000,00
730 Dec 2025Fuel cards860,40-
8
Formula cell (D2)InputMarks: extra spaceNote: C4 holds a leading and a trailing space. The formula needs the dynamic array engine of Excel 2021 or Microsoft 365 for the whole range and for LET.

Formula in D2

=LET(
  txt, TRIM(C2:C7),
  isNeg, RIGHT(txt, 1) = "-",
  body, IF(isNeg, LEFT(txt, LEN(txt) - 1), txt),
  amount, NUMBERVALUE(body, ",", "."),
  SUM(IF(isNeg, -amount, amount))
)
=LET(
  txt; TRIM(C2:C7);
  isNeg; RIGHT(txt; 1) = "-";
  body; IF(isNeg; LEFT(txt; LEN(txt) - 1); txt);
  amount; NUMBERVALUE(body; ","; ".");
  SUM(IF(isNeg; -amount; amount))
)

Result: 9,533.859.533,85

Step by step
  1. txt is TRIM(C2:C7): six texts without the outer spaces. " 720,00 " becomes "720,00"; the others stay as they were.
  2. isNeg tests the last character of each text: FALSE, TRUE, FALSE, TRUE, FALSE, TRUE. Rows 3, 5 and 7 are debits.
  3. body removes the trailing minus where isNeg is TRUE: "1.250,00", "3.480,50", "720,00", "95,25", "12.000,00", "860,40".
  4. amount applies NUMBERVALUE to all six texts at once: 1,2501.250; 3,480.53.480,5; 720; 95.25; 12,00012.000; 860.4.
  5. The IF in the last line flips the debits to negative: 1,2501.250; -3,480.53.480,5; 720; -95.25; 12,00012.000; -860.4.
  6. SUM adds them: receipts 1,2501.250 + 720 + 12,00012.000 = 13,97013.970; debits 3,480.53.480,5 + 95.25 + 860.4 = 4,436.154.436,15; net 13,97013.970 - 4,436.154.436,15 = 9,533.859.533,85.

Watch out: The opposite mistake: a minus at the front is already understood by NUMBERVALUE. If you strip every hyphen with SUBSTITUTE instead of testing the last character, "-95,25" loses its sign silently and the total is too high by twice that line.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

NUMBERVALUE accepts a whole range and returns an array, so one LET replaces a helper column. It ignores spaces by itself, but the test for the trailing minus does not, which is why TRIM sits in front of RIGHT. The trailing minus is not part of what NUMBERVALUE reads, so the sign is handled first, outside the function.

For a bank file you import every month, Power Query is better (set the locale once, then refresh); for a single column you need once, Text to Columns with the Advanced button converts it in place and even has a "Trailing minus for negative numbers" option, but the original text is gone, whereas the formula keeps it next to the result for the audit file.

More to watch out for

  • An empty line in C2:C7 gives 0 and a line with text such as "n/a" makes the whole SUM #VALUE!. Compare COUNTA(C2:C7) with the number of lines on the statement before trusting the total.

Variations

=IF(RIGHT(TRIM(C2), 1) = "-", -1, 1) * NUMBERVALUE(SUBSTITUTE(C2, "-", ""), ",", ".")
=IF(RIGHT(TRIM(C2); 1) = "-"; -1; 1) * NUMBERVALUE(SUBSTITUTE(C2; "-"; ""); ","; ".")

One line per cell, copied down, when you need the signed amount per row and not only the total. It assumes the minus only ever appears at the end.

Common mistakes

SymptomCauseFix
Different numbers on different computersYou leave out both separators on a file from another country, and the same text gives a different number on each regional setting.Always state the separators of the source file: NUMBERVALUE(A2, ",", ".") for 1.234,56. Then the result no longer depends on who opens the workbook.
#VALUE! on trailing minus or currencyThe text is "1.234,56-" or "EUR 1.234,56" and the result is #VALUE!.A trailing minus, a currency symbol or a letter is not part of a number. Strip a currency symbol or a letter first with SUBSTITUTE. For a trailing minus, test it with RIGHT and move it to the front with LEFT and LEN, as the Master example does.
#VALUE! with the same separator twiceYou give the same character for both separators, for example NUMBERVALUE(A2, ",", ","), and get #VALUE!.Decimal and group separator must differ. If you do not need a group separator, leave the argument out, but then check that the regional setting's group separator is not the same character as your decimal separator.
A missing amount counts as 0Blank-looking cells give 0 instead of an error, so a missing amount silently counts as zero in the total.Count the lines first: compare COUNTA of the text column with the number of numeric results, or add a check that flags LEN(A2) = 0.