I'll show you 4 ways to convert numbers stored as text to numbers in Excel. This situation often happens when you export data from another program to Excel and it can wreak havoc in your spreadsheets.
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.)
(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.
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.
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.
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.
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.
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.
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.