Function guides ยท Text
VALUE function
Converts text that looks like a number into a real number.
Syntax
=VALUE(text)
| Argument | What it is |
|---|---|
text | Text in a recognizable number, date or time format, or a cell holding it. |
What VALUE does
VALUE turns text that looks like a number into a real number. The cell may show 250, but if it is text, SUM ignores it and the result is a quiet 0.
It understands the same formats Excel does when you type: plain digits, currency with a symbol, percentages, dates and times. A date becomes its serial number, which you then format as a date.
Text functions are the usual cause of the problem. LEFT, MID, RIGHT and SUBSTITUTE all return text, so anything built from their output needs VALUE before it is used in arithmetic.
How do you know a cell is text? A number stored as text is left-aligned by default, often has a small green triangle in the corner, and returns FALSE for ISNUMBER. Select such cells and Excel offers Convert to Number from the warning icon, which fixes them in place without a formula.
For a quick fix in a formula, adding zero or multiplying by one does the same job as VALUE and is slightly shorter. VALUE is the clearer choice when someone else will read the formula.
VALUE examples
1. Number pulled out of text
| A | B | |
|---|---|---|
| 1 | Code | Quantity |
| 2 | BOX-24 | 48 |
LEFT, MID and RIGHT return text, even when the characters are digits. VALUE converts the piece so it can be added. Here the quantity is the part of the code after the dash.
2. A currency string
| A | B | |
|---|---|---|
| 1 | Imported | |
| 2 | $1,250.50 | 1250.5 |
Text such as "$1,250.50" is understood, with the currency symbol and thousands separator, and returned as the number 1250.5.
3. A date written as text
| A | B | |
|---|---|---|
| 1 | Text date | |
| 2 | 3/14/2026 | 03/14/2026 |
A date that arrived as text, from a CSV file for example, will not sort or subtract properly. VALUE turns "3/14/2026" into a serial number, which the format mm/dd/yyyy then displays as a date.
Common VALUE mistakes
- #VALUE! on text that is not numeric. Letters or stray characters stop the conversion. Clean with SUBSTITUTE or TRIM first.
- Spaces and non-breaking spaces. A leading space is fine, but a non-breaking space (character 160) is not. Substitute it.
- Assuming it keeps leading zeros. VALUE("0932") is the number 932. The zeros are gone.
- Locale dependence. Whether "1.234,5" or "1,234.5" is understood depends on regional settings.
VALUE on Excel job tests
The classic version: "the sales column was imported as text, so SUM returns 0, fix it". The quickest repair for a whole column is Text to Columns, or Paste Special with Multiply on a cell containing 1. A formula answer uses VALUE or a double minus sign.
VALUE is also the second half of text extraction. LEFT, MID and RIGHT return text, so anything that goes on to arithmetic or a numeric lookup needs it.
Check the result with ISNUMBER on one cell, and read the task for whether the fix goes in place or into a new column.
Related functions
VALUE questions
- What does VALUE do in Excel?
- It converts text that looks like a number, date or time into the real value.
- How can I convert a whole column of text numbers?
- Select it, use Data > Text to Columns > Finish, or multiply a spare cell containing 1 by the range with Paste Special.
- Why does VALUE return
- The text contains characters that are not part of a number. Clean it first.
- Does VALUE keep leading zeros?
- No. Use TEXT afterward if you need them back for display.
Practice VALUE
Try it in a working grid: the free lesson LEFT, RIGHT and MID: pull out part of a cell (module 9 of Everyday Excel) has checked VALUE exercises.
Practice this function