Excel VALUE Function:
Convert Text to Numbers

The VALUE function converts text that looks like a number into a real number.

The syntax is =VALUE(text). For example, if cell A2 contains the text “1,000”, =VALUE(A2) returns the number 1000. And =VALUE("$1,000") also returns 1000.

Here’s the quick version:

  1. In an empty cell, type =VALUE(
  2. Click the cell with the number stored as text (or type the text in quotes).
  3. Type ) and press Enter.

You need VALUE when numbers are stored as text and your lookups, sums or other formulas stop working. Below, I’ll show you a real example, more examples, and what to do when VALUE returns a #VALUE! error 👇

As always, the best way to learn how a function works is by looking at an example. Let’s get right into it!

Kasper Langmann, Microsoft Excel & Copilot MVP

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

How to use the ‘VALUE’ function

There are cases where numerical values are stored in cells as text, and we need to convert them to a number.

As you’re about to see, the ‘VALUE’ function brings much-needed relief to some real data quality headaches.

VALUE function

When we type in ‘=VALUE’ the tooltip clearly tells us what the function does:

“Converts a text string that represents a number to a number”

The syntax is as simple as it gets: =VALUE(text). The only argument is the text that needs to be converted. It can be text in quotation marks or a reference to the cell that contains the text.

The text has to look like a number, date or time that Excel recognizes. Otherwise VALUE returns the #VALUE! error.

VALUE formula

It’s not super common to have the need for the ‘VALUE’ function. But as with anything, there are exceptions to this!

Sometimes you need ‘VALUE’ when calculations (or other formulas that rely on a value stored as text) do not work as intended. This can be a real headache.

Kasper Langmann, Microsoft Excel & Copilot MVP

For instance, when a part number (or product code) that a lookup formula relies on is not only stored as text but may even have trailing spaces.

“Trailing spaces?! But we can use the TRIM function to remove that!” That’s true, and a great suggestion by the way! But it won’t solve our problem as it won’t convert text to numbers.

Kasper Langmann, Microsoft Excel & Copilot MVP

And if our lookup table contains number value data, we still haven’t solved all our issues.

The ‘VALUE’ function fixes both issues in one step.

Let’s take a closer look!

Lookup table

Our lookup table is based on product codes that are number format.

Thus, it is necessary that the data we will be indexing to this table will also be in number format.

However, it is not uncommon that raw data can come in many formats other than what we expect.

Kasper Langmann, Microsoft Excel & Copilot MVP

To learn how to solve this, I’ve setup an example for you. Here our raw data product codes are in text format with trailing spaces.

For further illustration, the following figure shows that we have cleansed our data using the ‘TRIM’ function first.

Kasper Langmann, Microsoft Excel & Copilot MVP

Then we’ve used the ‘VALUE’ function. Notice that at a glance, the data in all three columns appears the same.

TRIM function
Using VALUE function

But the real issue becomes evident once we attempt to build our formula to index our data to the lookup table.

INDEX function

The only formula that works is the one in the ‘Lookup 3’ column.

This references the values in column C where we have used the ‘VALUE’ function.

The ‘VALUE’ function not only converted the text version of the product code data in the ‘Original Data’ column… It also removed the trailing spaces in the process!

Kasper Langmann, Microsoft Excel & Copilot MVP

The ‘TRIM’ function removed trailing spaces for us… but it didn’t address the issue of the original data being a text representation of our product codes. Because of this our formulas in the ‘Lookup 2’ column return errors.

Our ‘Lookup 1’ column formulas reference the original data.

This illustrates the basic point that numbers stored as text will not match actual number values in our lookup table. The same goes for VLOOKUP and XLOOKUP.

Kasper Langmann, Microsoft Excel & Copilot MVP

VALUE function examples

Here are a few more examples of what VALUE returns:

  • =VALUE("$1,000") returns 1000. VALUE understands currency symbols and thousands separators.
  • =VALUE("16:48:00")-VALUE("12:00:00") returns 0.2. Times become fractions of a day (0.2 is 4 hours and 48 minutes).
  • =VALUE(A2), where A2 holds the text “17456 ” with a trailing space, returns 17456, as you saw in the example above.
  • =VALUE("abc") returns #VALUE!, because “abc” doesn’t look like a number.

Remember that VALUE gives you a new number in a new cell. The original text stays as it is. To replace the original, copy the VALUE results and paste them over the original cells as values.

Tip: Excel often converts text to numbers on its own when you use the text in a calculation. That’s why =A2*1 or =A2+0 also turn a number stored as text into a number. VALUE is the clearest way to show what you’re doing, though.

Why VALUE returns #VALUE!

If VALUE returns #VALUE!, Excel doesn’t recognize the text as a number, date or time. The most common reasons:

  • The text contains letters or other characters, like “17456 pcs” or “N/A”. Remove them first, for example with SUBSTITUTE.
  • The text contains non-breaking spaces (character 160), which is common in data copied from web pages. Remove them first: =VALUE(SUBSTITUTE(A2,CHAR(160),"")). More on that in my guide to removing extra spaces.
  • The decimal or thousands separator doesn’t match your regional settings, like “2.500,27” on a computer that uses a period as the decimal separator. Use NUMBERVALUE instead and tell it which separators the text uses: =NUMBERVALUE(A2,",",".") returns 2500.27.

For other causes of the error, see how to fix the #VALUE! error.

Need to convert a whole column of numbers stored as text without a formula? Then the error-checking button, Text to Columns or Paste Special are faster. I show all of them in my guide on how to convert text to numbers in Excel.

Conclusion: Wrapping up…

Now you know how VALUE turns numbers stored as text into real numbers, and how to fix it when it returns #VALUE! :)

VALUE’s use is relatively specialized compared to many other functions in Excel (like VLOOKUP). But when you need it, it sure is one function worth keeping in your bag of tricks!

Kasper Langmann, Microsoft Excel & Copilot MVP

Frequently asked questions

What does the VALUE function do in Excel?

It converts text that represents a number (or a date or time) into a real number. =VALUE("1,000") returns 1000.

Why does the VALUE function return #VALUE!?

The text isn’t in a number, date or time format Excel recognizes. Look for letters, non-breaking spaces or a decimal separator that doesn’t match your settings. See why VALUE returns #VALUE!.

What’s the difference between VALUE and NUMBERVALUE?

VALUE uses your computer’s regional settings to read the number. NUMBERVALUE lets you set the decimal and group separators yourself, so it can read numbers written in another format, like “2.500,27”.

Can VALUE convert dates?

Yes. VALUE turns a date written as text into Excel’s date serial number. Format the cell as a date to see it as a date again.

How do I convert text to numbers without a formula?

Select the cells, click the small warning icon next to them and choose Convert to Number, or use Text to Columns or Paste Special. See how to convert text to numbers in Excel.