Wauvel

Excel like a finance pro.

← All functions

VALUE & NUMBERVALUE

Text & cleanup

Numbers that arrive as text don't sum — and the total quietly reads zero.

Difficulty

Amateur
Excel file

1What is it?

A column that looks like numbers and adds up to zero is text. It happens constantly with exports, CSVs from other countries and anything pasted out of a PDF. VALUE converts text that looks like a number into a real one. NUMBERVALUE does the same but lets you say what the decimal and thousands separators are, which is the only way to read "1.234,56" — the European form — correctly on a machine set up for the US. Get that wrong and you don't get an error; you get a number a thousand times off.

2What it looks like

VALUE(text)   ·   NUMBERVALUE(text, [decimal_separator], [group_separator])
text
The text that should be a number. Currency symbols and thousands separators in the local format are handled.
[decimal_separator]
NUMBERVALUE only: the character used as the decimal point in THIS text — "," for European data.
[group_separator]
NUMBERVALUE only: the thousands separator — "." for European data.

3When you use it

  • Fix an exported amount column that sums to zero.
  • Read a supplier's European-format invoice amounts correctly.
  • Convert percentages pasted as text like "12.5%".

4See it in action

Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.

Pick what the export gave you. The European one is where VALUE goes quietly wrong.

A2
fx
=NUMBERVALUE(A2, ",", ".")VALUE on a US machine → #VALUE!, or 1.23456 if the comma is dropped — a thousand times off, with no error.
A
1Result
2VALUE on a US machine → #VALUE!, or 1.23456 if the comma is dropped — a thousand times off, with no error.
3NUMBERVALUE with the separators STATED → 1234.56.

The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.

5Common errors

#VALUE!The text contains something that isn't part of a number — a trailing space, a non-breaking space, a stray letter.

Fix: Clean it first: VALUE(TRIM(SUBSTITUTE(A2, CHAR(160), " "))).

Out by a factor of a thousandEuropean "1.234,56" was read by VALUE on a US machine, which took the dot as a decimal.

Fix: NUMBERVALUE with the separators stated explicitly. This is the whole reason it exists.

The column still won't sumThe formula was written but the original text column is what the total points at.

Fix: Point the total at the converted column, or paste the results back as values.

Negatives in brackets stay text"(1,200)" isn't a format VALUE recognises everywhere.

Fix: SUBSTITUTE the brackets to a leading minus first.

6Better functions & alternatives

  • Text to Columns → Finish — Converts a whole column in place with no formula. Usually the fastest fix. See the tip.
  • Paste Special → Multiply by 1 — Another no-formula conversion that also works on a whole range at once.

Want VALUE & NUMBERVALUE already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.