Excel VALUE Function — Convert Text to Numbers (with Examples)

What does it do?

VALUE converts a text string that looks like a number into an actual numeric value that Excel can use in calculations. When you import data from CSV files, databases, or web exports, numbers often arrive as text — SUM returns 0, averages are wrong, and lookups fail. VALUE fixes this.

Syntax

=VALUE(text)

Argument	Description
=VALUE(text)
text required	A text string containing a number, or a cell reference containing such a string.

Real examples

Example 1

Convert imported number to real number

=VALUE(A2)

Turns the text "1234" into the number 1234.

Example 2

Fix a percentage stored as text

=VALUE(A2)/100

Turns "15.6" into 0.156 — usable as a percentage.

Example 3

Convert currency text

=VALUE(SUBSTITUTE(A2,"$",""))

Remove the $ sign first, then convert to number.

Example 4

Clean and convert

=VALUE(TRIM(A2))

Remove spaces then convert — handles numbers with leading/trailing spaces.

Example 5

Use in SUMPRODUCT for text numbers

=SUMPRODUCT(VALUE(A2:A10))

Sums a range of numbers stored as text.

FAQ

How do I know if a number is stored as text?

Look for a green triangle in the top-left corner of the cell, or left-aligned numbers (numbers normally right-align). =ISNUMBER(A2) returns FALSE for text-numbers.

Why is my SUM returning 0 for imported data?

Your numbers are stored as text. Use VALUE to convert them, or select the column and use Data → Text to Columns → Finish as a quick fix.

What is the difference between VALUE and a number format?

Formatting only changes how a cell looks. VALUE changes the actual stored value from text to a number.

When numbers become text — and how to fix them

Numbers stored as text are one of the most frustrating problems in Excel because they are invisible. The cell looks exactly like a number, but Excel treats it as text. The symptoms: SUM returns 0, AVERAGE is wrong, VLOOKUP fails to match, and sorting puts 10 before 2 (alphabetical order). The green triangle warning in the cell corner is the telltale sign.

This happens most commonly when importing from CSV files (where everything is treated as text by default), from database exports, from web scraping, and when numbers are formatted with thousand separators or currency symbols in the source system.

VALUE fixes this, but sometimes you need to clean the value first. =VALUE(TRIM(A2)) handles leading/trailing spaces. =VALUE(SUBSTITUTE(A2,",","")) handles numbers formatted with commas like "1,234". =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) handles currency strings like "$1,234".

💡 Quick fix without formulas

Select the column of text-numbers, click the yellow warning diamond that appears, and choose "Convert to Number". This permanently converts without needing a VALUE formula. Use VALUE when you need the conversion inside a larger formula.

Practise this formula live

ExcelPro has exercises covering this formula across multiple tracks. Free to start.

Try exercises →

Related formulas

TRIM SUBSTITUTE TEXT LEFT SUMPRODUCT IFERROR

The Excel VALUE Functionexplained simply