Convert text to numbers

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 added spaces, hyphens, commas, etc., to a number, changing it to text.
4. The output of another formula is text, but expected to be a number.

Regardless of the cause, the problem is that a value that looks like a number but is actually text can't be used in Excel like a true number. If you try to use it in a formula, you will typically get a #VALUE! error

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 (+0) trick

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.

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)
5. 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, commas, punctuation, and so on, you'll need to remove these characters before you can convert the value to a number. This calls for a more aggressive approach. If you are using a current version of Excel that has the REGEXREPLACE function, this formula will do it in one step:

=REGEXREPLACE(A1,"[^0-9]","")+0

In a nutshell, this formula uses regular expressions to replace every character that is not strictly a number with nothing. Then we add 0 to convert the text result into a numeric value. The nice thing about this approach is that it will handle all kinds of extra characters that are not numbers, both visible and non-visible. There is no need to identify and specify which characters to remove. For details, see Strip non-numeric characters.

In older versions of Excel without the REGEXREPLACE function, we need to take a more manual approach. 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:

=LEFT(B12,2)+0 // left 2 characters
=RIGHT(B13,3)+0 // right 3 characters
=RIGHT(B14,4)+0 // right 4 characters

Each of these formulas extracts a specific number of characters from the left or right side of the text string. Then we add 0 to the result to make Excel convert the result to a numeric value.

To extract a number at a known location in the middle of a text string, use the MID function.