VALUE function
Quick answer
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)
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.
Answers that use VALUE
In other languages
VALUE is VALOR in Spanish Excel and WERT in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .