# VALUE function in Excel

> VALUE converts text that looks like a number into a real number Excel can add, average and compare.

Source: https://spreadsheetpractice.com/functions/value · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** VALUE converts text that looks like a number into a real number Excel can add, average and compare.
- **Syntax:** `=VALUE(text)`
- **Most common use:** `=VALUE(A2)` returns 1250 (Text with a comma becomes the number 1250)
- **Available in:** Every current version
- **Category:** [Text functions](https://spreadsheetpractice.com/functions/category/text)
- **Returns:** A number, or #VALUE! if the text can't be read as one.
- **Google Sheets:** Works the same in Google Sheets.

## What it does

VALUE converts text that looks like a number into a real number Excel can add, average and compare. Imported data often stores numbers as text, which is why SUM returns 0 and lookups miss; VALUE fixes that inside a formula.

## Syntax

`=VALUE(text)`

| Argument | Required? | What it is |
|---|---|---|
| `text` | Required | Text that looks like a number, date or percentage, such as "1,250", "$12.50" or "12%". |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | `=VALUE(A2)` | 1250 | Text with a comma becomes the number 1250 |
| D3 | `=VALUE(MID(B3,5,4))` | 2025 | The year from an invoice code, as a number |
| D4 | `=VALUE(C4)*1000` | 5 | A text percentage becomes 0.005, ready for math |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D |
|---|---|---|---|---|
| 1 | Amount (text) | Invoice | Rate (text) | Result |
| 2 | 1,250 | INV-2026-0042 | 12% | 1250 |
| 3 | $89.50 | INV-2025-1187 | 7.5% | 2025 |
| 4 | 420 | INV-2026-0310 | 0.5% | 5 |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | The text contains something that isn't part of a number, like "USD" or a nonbreaking space. Strip it with SUBSTITUTE first, then convert. |
| Using it where Excel already converts | Arithmetic converts numeric text on its own, so ="5"+1 is 6. VALUE matters most for SUM, which skips text, and for lookups, where the text "42" doesn't match the number 42. |
| A date turns into a big number | VALUE("3/7/2026") returns 46088, the date's serial number. Format the cell as a date to see it as one. |

## Tips

- Adding 0 (=A2+0) or a double minus (=--A2) does the same conversion and shows up often in older formulas.

## Related functions

- [TEXT](https://spreadsheetpractice.com/functions/text): TEXT turns a number or date into text in the format you choose, such as "$1,250.00" or "March 2026".
- [ISNUMBER](https://spreadsheetpractice.com/functions/isnumber): ISNUMBER returns TRUE when a value is a real number, which includes dates and times.
- [SUBSTITUTE](https://spreadsheetpractice.com/functions/substitute): SUBSTITUTE replaces specific text with new text everywhere it appears, or only at the occurrence you choose.

## Questions

### Why does SUM return 0 in Excel?

The numbers are probably stored as text, often marked with a green triangle. Convert them with VALUE in a helper column, or select the cells and choose Convert to Number from the warning icon.

### How do I convert text to a number in Excel?

Use =VALUE(A2), or multiply by 1 with =A2*1. To convert a whole column in place, select it and use Data > Text to Columns > Finish.

## Practice

A graded VALUE exercise ("Fix amounts stored as text") is on [the VALUE page](https://spreadsheetpractice.com/functions/value). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
