Spreadsheet Practice

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)

ArgumentRequired?What it is
textRequiredText 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.

CellFormulaResultWhat it shows
D2=VALUE(A2)1250Text with a comma becomes the number 1250
D3=VALUE(MID(B3,5,4))2025The year from an invoice code, as a number
D4=VALUE(C4)*10005A 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)InvoiceRate (text)Result
1,250INV-2026-004212%1250
$89.50INV-2025-11877.5%2025
420INV-2026-03100.5%5

Practice VALUE

Graded instantly, then re-checked on changed data.

Fix amounts stored as text

The amounts in column A were imported as text, so they won't add up. In B2, convert the amount in A2 to a number. Fill down to B6.

Common mistakes

What you seeWhy, 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 convertsArithmetic 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 numberVALUE("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 .