Convert text to numbers

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.

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:

The data has been imported from another application and Excel has misinterpreted values.
Data has been copy-pasted into Excel and Excel does not recognize values as numbers.
A user has accidentally entered numeric values as text values.
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.

Add zero

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:

Add a zero to an unused cell
Copy the cell
Select the values to convert
Open Paste Special (Control + Option + V)
Choose Values + Add

Video: How to perform in-place changes with Paste Special.

Strip all non-numeric characters

If a text value contains non-numeric characters like dashes, punctuation, and so on, you'll need to remove these characters before you can convert the value to a number. The formulas in rows 12, 13, and 14 show how to use the LEFT and RIGHT functions to strip non-numeric characters from a text value before it's converted to a number. For more complicated situations, the best option is usually to remove all non-numeric characters before attempting a conversion. For details, see Strip non-numeric characters.