VALUE function
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.
- Available in
- Every current version
- Category
- Text functions
- Returns
- A number, or #VALUE! if the text can't be read as one.
- Google Sheets
- Works the same in Google Sheets.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| 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 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Amount (text) | Invoice | Rate (text) | Result |
| 1,250 | INV-2026-0042 | 12% | 1250 |
| $89.50 | INV-2025-1187 | 7.5% | 2025 |
| 420 | INV-2026-0310 | 0.5% | 5 |
Practice VALUE
Graded instantly, then re-checked on changed data.
Fix amounts stored as text
B2, convert the amount in A2 to a number. Fill down to B6.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.
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.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.