## Summary

To convert text values to numbers, you can use the VALUE function, or simply add zero as described below. In the example shown, the formula in D5 is:

``````=VALUE(B5)
``````

As the formula is copied down it converts the text values in column B to the numbers seen in column D. Note that, by default, Excel will left-align text values and right-align numeric values.

## Generic formula

``=VALUE(A1)``

## Explanation

In this example, the goal is to convert the text values seen in column B to the numeric values seen in column D.  There are several ways to fix this problem in Excel, but this article focuses on a formula-based approach to convert text values to numbers. It also explains how to convert values in place with Paste Special, which does not require a formula.

### The problem

Sometimes Excel will incorrectly evaluate a number as a text value. There are many reasons this might happen, including:

1. The data has been imported from another application and Excel has misinterpreted values.
2. Data has been copy-pasted into Excel and Excel does not recognize values as numbers.
3. A user has accidentally entered numeric values as text values.
4. The output of another formula is text but expected to be a number.

### Checking for numbers and text

In this example, the values in column B are "stored as text". One way to understand how Excel is interpreting a value is to check the alignment. By default, Excel will align numeric values to the right side of a cell and text values to the left side of a cell, as you can see in the worksheet above. Alignment can be changed, however, so this is not a foolproof method of checking values.

Another way to check for numbers is to sum the values with the SUM function. The SUM function will ignore text values so if the result is zero, you know that you have text values. You can also test values more explicitly with the ISNUMBER function or the ISTEXT function. Both formulas below will return either TRUE or FALSE for a value in cell A1:

``````=ISNUMBER(A1) // test for numbers
=ISTEXT(A1) // test for text``````

### VALUE function

The VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. When VALUE can successfully convert a text value to a number, it will just work, and VALUE will return the corresponding number. In cases where VALUE is not able to convert a text value to a number, it will return a #VALUE error.

Another way to convert a text value to a number is to add zero with a formula like this:

``````=A1+0
``````

The math operation forces Excel to try to convert the text value to a number. If the conversion succeeds, a number will be returned. If the conversion fails, the result will be a #VALUE error.

Note: you will often see this trick in more advanced formulas because it's short and a little more flexible than the VALUE function.

### Add zero with Paste Special

If you want to perform an "in place" conversion of text to numbers without a formula, another solution is to add zero with a Paste Special command. This approach leaves the converted values in the same cells. The basic steps are as follows:

1. Add a zero to an unused cell
2. Copy the cell
3. Select the values to convert
4. Open Paste Special (Control + Option + V)