Text function

Excel VALUE function

Q: How do I convert text to numbers in Excel?

Use =VALUE(A2), or select the cells, click the warning icon, and choose Convert to Number. Multiplying by 1 also works.

VALUE converts text that looks like a number into an actual number. Use it when imported figures will not add up, or after LEFT, MID, or SUBSTITUTE return digits as text.

=VALUE(text)

Arguments

text: The text to convert, such as "42", "15%" or a cell holding a number as text.

VALUE examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C
1	Imported qty	Imported discount	Code
2	12	40	BX-0045
3	8	12.5%	BX-0120
4	15	5%	BX-0007

=VALUE(A2)*2
Result: 24
Turns the text "12" into the number 12, so the math works: 24.
=VALUE(B3)
Result: 0.125
Percent text converts to a decimal: "12.5%" becomes 0.125.
=VALUE(RIGHT(C3,4))
Result: 120
RIGHT returns "0120" as text. VALUE turns it into 120.
=VALUE("N/A")
Result: #VALUE!
Text that is not a number gives #VALUE!.

How to use VALUE

Click an empty cell next to the text number.
Type =VALUE( and select the cell.
Close the bracket and fill down.
Paste the results as values if you want to replace the originals.

Common VALUE mistakes

Hidden spaces

Non-breaking spaces from web data can stop VALUE working. Clean with SUBSTITUTE(A2,CHAR(160),"") first.

Converting real words

VALUE("N/A") is #VALUE!. Wrap it in IFERROR if some cells are not numbers.

VALUE FAQ

How do I convert text to numbers in Excel?

Use =VALUE(A2), or select the cells, click the warning icon, and choose Convert to Number. Multiplying by 1 also works.

Why is my SUM returning 0?

The numbers are probably stored as text, often shown left aligned with a green triangle. Convert them with VALUE first.

Is VALUE the same as NUMBERVALUE?

They are similar, but NUMBERVALUE lets you set the decimal and thousands separators, which helps with European formats like 1.250,00.