All versionsExamples need Excel 2021 or laterText

VALUE

WAARDE in Dutch Excel

Turns text that looks like a number, a percentage, a date or a time into a real number Excel can calculate with.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1RelationInvoice no.Open amount (text)Amount
2D100125-1180425042504250=VALUE(C2)=VALUE(C2)
3D100425-12031890
4D100725-121512600
5D100925-1231730
6D101225-12442150
Note: Every cell in C2:C6 holds text made of digits, not a number. =SUM(C2:C6) returns 0.

Result: 4250

=VALUE(C2)
=VALUE(C2)

What VALUE does and when to use it

What it does: it converts a number stored as text into a real number, for example an amount from a CSV export that SUM skips.

When to use it: when text-numbers must be summed, compared or looked up: Excel converts them itself in a calculation, but not in comparisons, lookups or SUM over a range. They arrive from imports and from text functions such as LEFT and MID. If the source uses another decimal separator than your regional setting, use NUMBERVALUE.

How Excel evaluates it

Excel takes the text and removes leading and trailing spaces. It then tries to read it with the rules it uses for typed input under the current regional setting. Those rules accept:

  • digits
  • an optional sign in front
  • a decimal separator
  • thousands separators
  • a percent sign
  • a currency symbol
  • a date or time pattern

If the whole text fits one of those patterns, it returns the number, so 15% becomes 0.15 and a date becomes its serial number. If any part does not fit, the result is #VALUE!.

Watch out: VALUE reads text under your own regional setting, so the same text can give a number on one computer and an error on another.

Mental model

VALUE is Excel pretending you typed the text into an empty cell. Whatever that cell would have become as a number, VALUE returns; if the cell would have stayed text, VALUE returns #VALUE!.

Terms used on this page: serial number, range, cell

Syntax

Official VALUE documentation
VALUE(text)
VALUE(text)
ArgumentTypeMeaning
textRequiredThe text to convert; its decimal separator must match your regional setting.

Return value:A number: the text converted to a number

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

Turn exported receivables amounts into numbers

Veldhuis Installatie BV sends its receivables aging list (ouderdomsanalyse debiteuren) as an export in which the open amounts arrive as text: left-aligned, with a green triangle in each cell. You want to reconcile the total to account 1300 Debiteuren, but the total says 0. You add a column that converts each amount to a number so the total works.

ABCDE
1RelationInvoice no.Open amount (text)Amount
2D100125-1180425042504250=VALUE(C2)=VALUE(C2)
3D100425-12031890
4D100725-121512600
5D100925-1231730
6D101225-12442150
7
Formula cell (D2)InputNote: Every cell in C2:C6 holds text made of digits, not a number. =SUM(C2:C6) returns 0.

Formula in D2

=VALUE(C2)
=VALUE(C2)

Result: 42504250

Step by step
  1. Excel reads C2 and finds the text 4250: four characters, 4, 2, 5 and 0.
  2. VALUE checks whether that text would be a number if you typed it into a cell. Four digits with no separator fit the pattern in every regional setting.
  3. It returns the number 4250. D2 shows 4250, aligned right, without a green triangle.
  4. Copy D2 down to D6. Now =SUM(D2:D6) gives 4250 + 1890 + 12600 + 730 + 2150 = 21620, while =SUM(C2:C6) still gives 0. You can now compare 21620 with the balance of account 1300.
  5. Test it with =ISNUMBER(C2), which is FALSE, and =ISNUMBER(D2), which is TRUE. The value looks identical; only its type changed.

Watch out: If one amount was typed as 1 200 with a space as thousands separator, VALUE may return #VALUE! for that row. The total then turns into an error, which at least shows you where to look.

More on this example: why it works, 2 variations

Why it works

SUM skips text in a range on purpose, so a column of text-numbers adds up to 0 without an error. That is dangerous in an audit: a total of 0 looks like a finding when it is only a data type.

VALUE changes the type, not the digits, and SUM counts the converted column. Whole numbers without separators are read the same way in every regional setting, which is why this example gives 21620 on any computer.

Variations

=--C2
=--C2

The double minus: the first minus forces arithmetic, which converts the text, and the second restores the sign. Same result, shorter, common in array formulas.

=SUM(VALUE(C2:C6))
=SUM(VALUE(C2:C6))

In Excel 2021 or Microsoft 365 one formula converts and totals the whole column: 21620, without a helper column.

Intermediate

Read VAT rates that arrive as text

You recalculate the VAT on sales invoices of the client. The invoice export holds the VAT rate as text such as 21%. One invoice is marked exempt, so there is no rate to read.

You need the rates as real percentages to multiply with the net amounts, and an exempt invoice should count as 0.

ABCDE
1Invoice no.Net amountVAT rate (text)VAT rate
225-0412€12,500.00€ 12.500,0021%21%21%=IFERROR(VALUE(C2), 0)=IFERROR(VALUE(C2); 0)
325-0413€3,800.00€ 3.800,009%
425-0414€6,400.00€ 6.400,00exempt
525-0415€920.00€ 920,0021%
625-0416€15,000.00€ 15.000,0021%
7
Formula cell (D2)Input

Formula in D2

=IFERROR(VALUE(C2), 0)
=IFERROR(VALUE(C2); 0)

Result: 21%21%

Step by step
  1. Excel evaluates VALUE(C2) first. C2 holds the text 21%: two digits and a percent sign, no decimal separator.
  2. A percent sign is part of what Excel accepts as typed input, so VALUE reads 21% as 21 / 100 = 0.21. The percent sign is the same in both regional settings, so the result is 0.21 everywhere.
  3. VALUE did not return an error, so IFERROR passes 0.21 through. D2 shows 21% once you format it as a percentage.
  4. Copy D2 to D4. C4 holds the text exempt, which Excel cannot read as a number, so VALUE returns #VALUE!. Without IFERROR, D4 would show that error and every VAT amount built on it would fail too.
  5. With IFERROR, the error is replaced by the second argument, 0. D4 shows 0%, meaning no VAT, which is what an exempt invoice should carry.
  6. The recalculated VAT of invoice 25-0412 is then 12500 × 0.21 = 2625. For 25-0414 it is 6400 × 0 = 0. Over all five invoices: 2625 + 342 + 0 + 193.2 + 3150 = 6310.2, which you compare with the VAT in the client's return.

Watch out: IFERROR also catches a typo. If someone types 2l% with a lowercase L, D shows 0% and the VAT silently disappears from your recalculation. Count the zeros in the column against the number of exempt invoices to spot it.

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

Why it works

VALUE understands the percent sign because it reads text the way Excel reads typed input, and that makes the conversion independent of the decimal separator. IFERROR turns the one expected failure, a text that is not a number, into an audit rule: exempt means no VAT. The conversion and the fallback are separate steps, so a reader can see which one produced each result.

More to watch out for

  • A rate such as 4.5% contains a decimal separator and would be read according to the regional setting. Keep such lists in whole percentages, or see the Master example.

Variations

=B2 * IFERROR(VALUE(C2), 0)
=B2 * IFERROR(VALUE(C2); 0)

The recalculated VAT in one step: 2625 for invoice 25-0412.

=B2 * C2
=B2 * C2

Arithmetic converts 21% by itself and also gives 2625, but returns #VALUE! for the exempt row. It shows that VALUE is not needed for the conversion here, only for control over the failure.

AdvancedExcel 2021 or later

Match a bank description to a receivable by invoice number

You match bank receipts to the receivables list. The bank reference looks like BNK-10452-D1001: the journal, the invoice number and the relation. The aging list stores invoice numbers as real numbers.

You extract the number with MID and look up the customer, and the lookup keeps returning Not found until the extracted text becomes a number.

ABCDEFG
1Invoice no.CustomerOpen amountBank referenceCustomer
21044810448Bakkerij De Graaf BV€2,150.00€ 2.150,00BNK-10452-D1001Hoekstra Installatie BV=XLOOKUP(VALUE(MID(E2, 5, 5)), A2:A6, B2:B6, "Not found")=XLOOKUP(VALUE(MID(E2; 5; 5)); A2:A6; B2:B6; "Not found")
31045010450Jansen Transport BV€8,400.00€ 8.400,00
41045210452Hoekstra Installatie BV€4,250.00€ 4.250,00
51045510455Mulder Groep BV€730.00€ 730,00
61045710457Smit Logistiek BV€12,600.00€ 12.600,00
7
Formula cell (F2)Input

Formula in F2

=XLOOKUP(VALUE(MID(E2, 5, 5)), A2:A6, B2:B6, "Not found")
=XLOOKUP(VALUE(MID(E2; 5; 5)); A2:A6; B2:B6; "Not found")

Result: Hoekstra Installatie BV

Step by step
  1. Excel evaluates the innermost function first. MID(E2, 5, 5) starts at the fifth character of BNK-10452-D1001 (B, N, K and the hyphen come first) and takes 5 characters: the text 10452.
  2. VALUE turns the text 10452 into the number 10452. There is no decimal separator in it, so the regional setting plays no role.
  3. XLOOKUP searches A2:A6 for the number 10452: 10448, no; 10450, no; 10452, yes. The match is in the third cell, A4.
  4. It returns the third cell of B2:B6, which is B4: Hoekstra Installatie BV. The open amount on that invoice, 4250, is what the receipt should settle.
  5. Without VALUE, XLOOKUP receives the text 10452 and compares it with the number 10452. A lookup does not convert types, so text never equals a number; every row fails and F2 shows Not found.
  6. Change E2 to BNK-10457-C2001 and F2 shows Smit Logistiek BV.

Watch out: A reference with a six-digit invoice number, such as BNK-104521-D1001, still gives five characters from MID: 10452. The formula silently returns Hoekstra Installatie BV for another invoice. If numbers can grow, split on the hyphens instead of counting characters.

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

Why it works

Text functions such as MID always return text, even when every character is a digit. XLOOKUP compares type and content, so the extracted number has to become a real number before it can match the aging list. VALUE sits exactly at the seam between the text world of the bank reference and the number world of the subledger.

More to watch out for

  • If some invoice numbers in column A are themselves stored as text, VALUE on the lookup side makes those rows unreachable. Check column A with ISNUMBER before trusting a Not found.

Variations

=XLOOKUP(--MID(E2, 5, 5), A2:A6, B2:B6, "Not found")
=XLOOKUP(--MID(E2; 5; 5); A2:A6; B2:B6; "Not found")

The double minus does the same conversion as VALUE; shorter, but less clear to the next reader.

=XLOOKUP(MID(E2, 5, 5), A2:A6 & "", B2:B6, "Not found")
=XLOOKUP(MID(E2; 5; 5); A2:A6 & ""; B2:B6; "Not found")

Converts the other side instead: A2:A6 & "" turns the numbers into text. Prefer this when the keys have leading zeros that must count.

MasterExcel 2021 or later

Bank statement amounts with a trailing minus and local decimals

The client's bank statement export, which you use to check the balance of account 1100 Bank around year end (31 December 2025), writes amounts as text with the sign behind the number, 1234.50- for a debit, and with the decimal separator of the regional setting it was exported under. VALUE rejects the trailing minus.

You build one conversion that handles the sign and keeps working for colleagues in both an English and a Dutch setting, as long as the export matches their setting.

ABCDE
1DateDescriptionAmount (text)Amount
229 Dec 2025Office rent1234.50--1,234.50-1.234,50=LET( raw, TRIM(C2), isNeg, RIGHT(raw, 1) = "-", digits, IF(isNeg, LEFT(raw, LEN(raw) - 1), raw), amount, VALUE(digits), IF(isNeg, -amount, amount) )=LET( raw; TRIM(C2); isNeg; RIGHT(raw; 1) = "-"; digits; IF(isNeg; LEFT(raw; LEN(raw) - 1); raw); amount; VALUE(digits); IF(isNeg; -amount; amount) )
330 Dec 2025Receipt from customer D1001860.00
431 Dec 2025Bank fees12.75-
52 Jan 2026Refund from supplier C200145.00
6
Formula cell (D2)InputNote: Column C is text as the bank wrote it. A reader with English settings sees a period as decimal separator, a reader with Dutch settings a comma, matching what VALUE expects on each computer.

Formula in D2

=LET(
  raw, TRIM(C2),
  isNeg, RIGHT(raw, 1) = "-",
  digits, IF(isNeg, LEFT(raw, LEN(raw) - 1), raw),
  amount, VALUE(digits),
  IF(isNeg, -amount, amount)
)
=LET(
  raw; TRIM(C2);
  isNeg; RIGHT(raw; 1) = "-";
  digits; IF(isNeg; LEFT(raw; LEN(raw) - 1); raw);
  amount; VALUE(digits);
  IF(isNeg; -amount; amount)
)

Result: -1,234.50-1.234,50

Step by step
  1. raw is TRIM(C2): the text 1234.50-, 8 characters. TRIM removes any space the export left at the end, which would otherwise hide the minus from the next step.
  2. isNeg compares the last character with a minus: RIGHT(raw, 1) is -, so isNeg is TRUE.
  3. digits drops that character: LEN(raw) - 1 = 8 - 1 = 7, and LEFT(raw, 7) is 1234.50.
  4. amount is VALUE(digits). In an English setting the period is the decimal separator, so 1234.50 becomes 1234.5. A Dutch reader's cell holds 1234,50, and in a Dutch setting the comma is the decimal separator, so the result there is also 1234,5.
  5. The final IF applies the sign: isNeg is TRUE, so the result is -1234.5. D2 shows -1,234.501.234,50 with a number format of two decimals and a thousands separator.

  6. Copied to D3: 860.00 has no trailing minus, isNeg is FALSE, digits is the whole text, and the result is 860. D4 gives -12.75, D5 gives 45.

    The four amounts add up to -1234.5 + 860 - 12.75 + 45 = -342.25. The last row is dated 2 January 2026, so for the cut-off at 31 December 2025 only the first three count: -1234.5 + 860 - 12.75 = -387.25.

Watch out: The mirror-image mistake is removing the minus with SUBSTITUTE(raw, "-", "") and converting the rest. Every amount converts cleanly and every debit becomes a credit: D2 shows 1,234.501.234,50 and the total becomes +2152.25 instead of -342.25.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

VALUE accepts a sign in front of a number, not behind it, so the formula moves the sign itself: detect it, cut it off, convert the rest, and apply it again. LET names each stage, and raw is read once. The locale-dependent part is isolated in one step, VALUE(digits), which follows the reader's regional setting.

VALUE accepts any format Excel recognizes, and recognized means recognized under your own regional setting: 1234.50 read on a Dutch computer is not 1234,5.

Do not use this construction when the export's decimal separator does not match the setting of the person opening it, for example a US bank file opened in Dutch Excel. Use instead:

  • NUMBERVALUE(digits, ".", ","), which fixes the separators regardless of setting
  • Power Query with the column locale set, the more robust route for recurring files, at the cost of a refresh step instead of a live formula
  • the Text to Columns wizard, quicker for a one-off file, which has a trailing-minus option under Advanced

More to watch out for

  • An English-format file opened in Dutch Excel can turn 1234.50 into a number 100 times too large, because the period is then read as a thousands separator, or into #VALUE!. The first is the dangerous one: it looks like a normal amount. Compare the column total with the bank statement before you rely on it.
  • A cell that holds only a minus, which some exports use for zero, gives an empty digits and VALUE returns #VALUE!. Decide whether that row means 0 and handle it before the conversion.

Variations

=LET(
  raw, TRIM(C2),
  isNeg, RIGHT(raw, 1) = "-",
  digits, IF(isNeg, LEFT(raw, LEN(raw) - 1), raw),
  amount, NUMBERVALUE(digits, ".", ","),
  IF(isNeg, -amount, amount)
)
=LET(
  raw; TRIM(C2);
  isNeg; RIGHT(raw; 1) = "-";
  digits; IF(isNeg; LEFT(raw; LEN(raw) - 1); raw);
  amount; NUMBERVALUE(digits; "."; ",");
  IF(isNeg; -amount; amount)
)

For a file whose separators may not match the reader's setting: NUMBERVALUE uses the decimal and group separators you pass it instead of the regional ones. Here they match the export shown above, a period for decimals and a comma for thousands; for a file with a comma as decimal separator you pass them the other way round.

=IF(RIGHT(TRIM(C2), 1) = "-", -VALUE(LEFT(TRIM(C2), LEN(TRIM(C2)) - 1)), VALUE(TRIM(C2)))
=IF(RIGHT(TRIM(C2); 1) = "-"; -VALUE(LEFT(TRIM(C2); LEN(TRIM(C2)) - 1)); VALUE(TRIM(C2)))

The same logic without LET for Excel 2019 and older; TRIM(C2) is calculated four times.

Common mistakes

SymptomCauseFix
Wrong number, or text, after a CSV importA CSV opened on a Dutch computer turns 1234.50 into 123450, or leaves it as text, while a colleague with English settings gets 1234.5.VALUE cannot fix a file written for another regional setting. Use NUMBERVALUE(A2, ".", ",") when the file always uses a period, or import with Power Query and set the locale of the column.
#VALUE! for text that looks like a numberVALUE returns #VALUE! for text that looks like a perfectly normal number.Look for a character you cannot see: a non-breaking space from a web page or a trailing minus from a bank export. Remove it with SUBSTITUTE first (the TRIM page shows how to find a non-breaking space), or see the Master example for the trailing minus.
Leading zeros disappear from a codeYou used VALUE on a product code such as 00452 and the leading zeros disappeared.A number has no leading zeros; 00452 becomes 452. Codes that are identifiers, not quantities, should stay text. Convert the other side of the comparison instead.
VALUE on a number changes nothingYou wrapped VALUE around a cell that already holds a number and expected a change.VALUE of a number returns the same number; it does nothing wrong, but it does nothing useful either. Check with ISNUMBER which cells are really text.