Converting numbers formatted as text

You may notice that Excel sometimes does not ‘see’ your numbers as numbers. So even though you can see it, when you try and add it up, Excel ignores it. You may notice that sometimes these numbers have a green triangle on the top left hand side of the cells. You can use these green triangles to help in converting numbers formatted as text into Excel acceptable numbers.

As shown below in cell B12 we are trying to add up the column yet the answer is obviously wrong. Notice the green triangles in the Excel cells. This is an error checking feature in Excel. Most people are desperate to remove the green triangles but it can be useful.

converting-numbers-formatted-as-text

If you click on one of the cells you will see an exclamation mark button and when you click on that you get the following

converting-numbers-formatted-as-text

Notice it tells you that the number is stored as text (the issue) but just below that is a Convert to Number option. If you click this it does what it says.

But you won’t want to do this with every cell individually, so rather highlight the column of cells BUT make sure your first cell contains the error (otherwise you don’t get the Exclamation button). Click on the button and choose Convert to Number and it will convert most numbers that areĀ a problem into a number

converting-numbers-formatted-as-text

Our Data Cleanup course addresses all these types of issues including where all these standard options don’t work.