You may notice that Microsoft 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.
Table of contents
YouTube- Convert numbers formatted as text
Using the green note to convert ‘text’ to 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.
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
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
Our Data Cleanup course addresses all these types of issues including where all these standard options don’t work.
Related
New Tool to clean data in excel 2013
Fixing date format or sort issues in Excel
Get and Transform- a new cleanup tool available free in Excel