Convert numbers stored as text to numbers

If you see cells with green triangles and numbers that are aligned to the left, then you have numbers stored as text. This can cause unexpected results and formatting problems. In addition when formulas are stored as text, the formula won't show the final result as a number. Instead, it will display as text and might look "frozen in time." The following process will show you how to convert these cells to numbers.

1. Select a column

Select a column with this problem. If you don't want to convert the whole column, you can select one or more cells instead. Just be sure the cells you select are in the same column, otherwise this process won't work.

2. Set the format

Press CTRL + 1 (or + 1 on the Mac). Then select any number format or currency format.

3. Click Data > Text to Columns

On the Data tab, click Text to Columns.

4. Click Finish

Click Finish right away and Excel will convert the cells.

Note: If you still see formulas that are not showing as numeric results, then you may have Show Formulas turned on. Go to the Formulas tab and make sure Show Formulas is turned off.

Other ways to convert:

Use a formula to convert from text to numbers

You can use the VALUE function to return just the numeric value of the text.

1. Insert a new column

Insert a new column next to the cells with text. In this example, column E contains the text stored as numbers. Column F is the new column.

2. Use the VALUE function

In one of the cells of the new column, type =VALUE() and inside the parentheses, type a cell reference that contains text stored as numbers. In this example it's cell E23.

3. Rest your cursor here

Now you'll fill the cell's formula down, into the other cells. If you've never done this before, here's how to do it: Rest your cursor on the lower-right corner of the cell until it changes to a plus sign.

4. Click and drag down

Click and drag down to fill the formula to the other cells. After that's done, you can use this new column, or you can cut and paste these new values to the original column. Here's how to do that: Select the cells with the new formula. Press CTRL + X. Click the first cell of the original column. Then on the Home tab, click the arrow below Paste, and then click Paste Special > Values.

Use Paste Special and Multiply

In this technique, you multiply each cell by 1 in order to convert from a text number to a regular number. Because you're multiplying the contents of the cell by 1, the result in the cell looks identical. However, Excel actually replaces the text-based contents of the cell with a numerical equivalent. This method is handy because other methods require the cells to be in one column, but this method does not.

1. Select a blank cell that doesn't have a green triangle and isn't aligned to the left.

2. Press CTRL + 1.

3. On the Number tab, click General, and then click OK.

4. In the cell, type 1, and then press ENTER.

5. Select the cell, and then press CTRL + C to copy the value.

6. Select the cells that contain the numbers stored as text that you want to convert.

7. On the Home tab, click the arrow below Paste, and then click Paste Special.

8. Under Operation, select Multiply, and then click OK.

See Also

Replace a formula with its result

Remove spaces and nonprinting characters from text

CLEAN function