The VALUE function in Excel
VALUE converts text that looks like a number, such as "12" or "$1,250.00", into a real number. Data exported from other systems often arrives as text, and then SUM quietly skips it and lookups return #N/A. VALUE is the quick fix, and spotting the problem is a classic job-test question.
Syntax
=VALUE(text)
| text | The text to convert, usually a cell. It can include a currency sign, thousands separators or a % sign. |
|---|
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
The quantities in column B were imported as text. In D2, calculate the line total: use VALUE to convert the quantity in B2, then multiply it by the unit price in C2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Line total | ||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: VALUE needs just one thing inside its brackets: the text cell. Then multiply by the price with `*`.
Examples
=VALUE(B2)→ 12Turns the text "12" into the number 12. Text sits on the left of a cell by default, and the result sits on the right like any number.
=VALUE(C2)→ 1250VALUE understands the dollar sign and the thousands separator, so "$1,250.00" becomes 1250.
=SUM(VALUE(B2:B4))→ 50=SUM(B2:B4) returns 0 here because every cell is text. In Microsoft 365, VALUE converts the whole range at once, so SUM can total it.
Example data
| 1 | Order | Qty (imported) | Price (imported) |
|---|---|---|---|
| 2 | A-1001 | 12 | $1,250.00 |
| 3 | A-1002 | 8 | $42.50 |
| 4 | A-1003 | 30 | $9.99 |
Common mistakes
Extra words in the cell
VALUE("12 units")returns #VALUE!, because the text isn't only a number. Remove the word first:=VALUE(SUBSTITUTE(B2," units","")).Hidden non-breaking spaces
Numbers copied from web pages often carry
CHAR(160)spaces that make VALUE fail. Strip them:=VALUE(SUBSTITUTE(B2,CHAR(160),"")).Trusting a total of 0
SUM ignores text without any warning, so a column of text numbers adds up to 0. If a total looks wrong, check the cells with
ISTEXTorISNUMBER.Separators from another region
VALUE reads numbers using your computer's settings, so
"1.250,50"fails in a US setup. NUMBERVALUE lets you name the decimal and group separators.
FAQ
How can I tell if a number is stored as text?+
Text numbers line up on the left of the cell, and Excel often marks them with a small green triangle. =ISTEXT(B2) returns TRUE for them and =ISNUMBER(B2) returns FALSE.
Is there a quicker way than VALUE?+
Any math converts text digits: =B2*1 or =--B2 give the same result. To fix a whole column in place, select it and use Data > Text to Columns > Finish.
Why does VLOOKUP return #N/A when the IDs look the same?+
One side is probably text and the other a number, and Excel treats "1001" and 1001 as different. Convert the lookup value: =VLOOKUP(VALUE(A2),D2:F20,2,FALSE).
Lesson
Master VALUE step by step: TEXT and VALUE: numbers to text and back
Format dates and dollar amounts inside sentences with TEXT, and turn digits stored as text into real numbers with VALUE.