Excel VALUE Function: Turn Text Into a Real Number
Converts a number that is stored as text into a real number.
VALUE fixes the single most common problem with imported data: numbers that Excel is treating as text. They look right, they line up in the column, and SUM quietly ignores every one of them. VALUE converts them back into numbers that arithmetic can reach.
The tell is alignment. Excel right-aligns numbers and left-aligns text by default, so a column of figures hugging the left edge of its cells is text. Another giveaway is a total that reads 0 when the rows visibly contain amounts.
It is fussier than people expect about what it will accept. A stray space, a currency symbol Excel does not recognise for your locale, or a thousands separator in the wrong position all produce #VALUE!. In practice VALUE is usually wrapped around TRIM and SUBSTITUTE rather than used alone.
Syntax
=VALUE(text)Arguments
textRequired- The text to convert. It must look like a number, a date or a time in a format Excel recognises for the current locale.
The example data
Headers in row 1, data in A2:C5. Column B arrived as text, not numbers.
| A | B | C | |
|---|---|---|---|
| 1 | Reference | Amount (text) | Score |
| 2 | INV-1041 | 1240.50 | 4 |
| 3 | INV-1042 | 385.00 | 2 |
| 4 | INV-1043 | 2100.75 | 5 |
| 5 | INV-1044 | 940.20 | 3 |
Worked examples
=VALUE(B2)Result: 1240.5
The text becomes a real number, right-aligned and available to SUM and every other calculation.
=SUM(B2:B5)Result: 0
The problem VALUE solves. SUM skips text silently, so a column of text amounts totals zero with no error at all.
=SUMPRODUCT(VALUE(B2:B5))Result: 4666.45
Converting the whole column inside the total, without a helper column. SUMPRODUCT handles the array.
=VALUE(TRIM(SUBSTITUTE(B2, "£", "")))Result: 1240.5
The realistic version: strip a currency symbol and any padding before converting, because VALUE rejects both.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#VALUE!
Why it happens: The text does not look like a number to Excel — a currency symbol, a stray space, or a decimal separator that does not match the locale.
How to fix it: Clean it first: =VALUE(TRIM(SUBSTITUTE(A2, "£", ""))). For a foreign decimal separator, NUMBERVALUE lets you specify it.
Still will not sum after converting
Why it happens: The conversion is in a helper column but the SUM still points at the original text column.
How to fix it: Point the total at the converted column, or convert the source in place with Text to Columns.
Result is a date serial number
Why it happens: The text looked like a date, so VALUE converted it to a date serial rather than a plain number.
How to fix it: That is correct behaviour. Format the cell as a date, or check the source really is meant to be a number.
Tips worth knowing
- The fastest bulk fix is not a formula at all: select the column, then Data → Text to Columns → Finish, which re-parses everything in place.
- Multiplying by 1 or adding 0 converts text numbers too, and is shorter than VALUE in a longer formula.
- NUMBERVALUE takes explicit decimal and group separators, which VALUE cannot — use it for data from another locale.
- Check with =ISTEXT(A2) if you are not sure whether a column is affected.
Frequently asked questions
Why does my SUM return 0 when the cells clearly have numbers?
Because they are stored as text and SUM skips text entirely, without warning. Check the alignment — text sits left, numbers sit right — and convert with VALUE or by running Text to Columns over the column.
How do I convert a whole column at once?
Select the column and use Data → Text to Columns → Finish. It re-parses every cell in place and needs no helper column, which makes it faster than VALUE for a one-off cleanup.
Why does VALUE return #VALUE! on something that looks like a number?
There is something in it Excel will not accept — most often a currency symbol, a non-breaking space from a web copy, or a decimal comma where the locale expects a point. Strip the extras with SUBSTITUTE and TRIM first, or use NUMBERVALUE to state the separators explicitly.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.