Convert Numbers Stored as Text to Numbers in Excel

Here are three simple ways to converts numbers to their proper number format:

(Don't forget to download the accompanying workbook so you can follow along with these examples.)

Method 1 - Use Excel's Built-in Help

(if you have thousands and thousands of rows, this method will take too long and you should use another method listed below)

This method will not always work, but when it does, it's by far the easiest.

When you have a set of numbers stored as text that Excel thinks should maybe be stored as numbers, it will warn you.  You will see a little green triangle in the upper-left corner of a cell:

When you see this, you can quickly convert all of those numbers to the correct format.

Let's start with the first column of numbers (Column A).

Note: This will only work on cells that are right next to each other.

1. Select all of the cells that have the green triangle:
   
2. You will notice a small box with an exclamation point appear, in this case over cell B2.  Click that and then click Convert to Number:
   
3. That's it, now all of these numbers are stored as numbers:
   You can tell that these are now stored as numbers because the numbers are now on the right-side of the cells instead of the left-side.

Method 2 - Paste Special

Another way to convert the numbers to the number format is to multiply the list by a number, use 1 so that it doesn't change any of the numbers.

We will work on column C this time.

1. Add 1 to an empty cell on the worksheet:
   
2. Copy the cell (Ctrl + C) where you just input 1:
   
3. Select the range of numbers to convert:
   
4. Right-click over the list of numbers and select Paste Special:
   
5. Select Multiply and hit OK:
   
6. Now, the entire column is formatted as a number.  Delete the 1 in cell D1 and that's it.:
   

Method 3 - Change the format of the Column

This is the simplest method but I put it at number 3 because it only works on the most basic numbers formatted as text.  Let's look to column E now.  Every number is formatted as text, but changing the format will only work on some cells.

1. Select the cells you want to change:
   
2. Go to the Home tab and in the Number box change where it says "Text" to "Number":
   
3. Look at the numbers now, this method only worked on the numbers from E7 to E11:
   

For those numbers you are done.  But this method did not work on the numbers from E1 to E6.  This is because those numbers have either a space or an apostrophe in front of them and simply changing the format will not fix that issue.  This is why method 1 and 2, though they require a little more work, will get you more consistently accurate results.

Method 4 - Text to Columns and Formatting

This method involves two big steps but it is fairly easy to use.  The problem with it, why I mention it last, is because it is easy for forget to perform the last step and because you leave some of the guess-work up to Excel, which I never like to do.

Let's go back to column E.

1. Select the column of numbers:
   
2. Go to the Data tab and click Text to Columns:
   
3. In the window that appears, don't touch anything, just click the Finish button:
   
4. Now, select the column of numbers again (we are almost done):
   
5. Go to the Home tab and, in the Number box, choose Number:
   
6. That's it!
   

You can remove the decimal places if you want by clicking the Decrease Decimal button, which is located inside the Number box on the Home tab.

Notes

I personally prefer Method 2 when I have to do this on a large list because it seems to work in the most consistent manner across all of my files.

If you need to perform this conversion on numbers that are not in a list or right next to each other, Method 2 is also probably the best option for doing that.

Remember that sometimes Excel won't tell you when a number is not formatted as a number; you need to be on the lookout for hidden apostrophes, leading and trailing spaces, and, of course, different formats for the cells.