Numbers seen as text in Excel

A common problem, especially when importing data from other systems is numbers seen as text in Excel and not being treated like numbers e.g. they don’t add up and you get VALUE error messages.

This can be due to a number of factors but you can generally address them in one of a few ways.

Learn- Data scrubbing of all kinds using Excel

VALUE function

The VALUE function is useful where you need to create a formula for regular use. As shown below the first column is the imported column and seems to be treated as text i.e. notice it is right aligned and in cell A12 where we have tried to SUM it, it is showing a zero. In column B we have created a formula that simply uses the VALUE function and points at the appropriate cell. This should convert numbers seen as text back into numbers.

numbers-seen-as-text-in-excelThis should work most of the time. However, occasionally this is not enough

Using the InBuilt Conversion Tool

Another way to convert these types of cells is to use the inbuilt conversion tool.

You will notice that when you have this issue, you have little green marks in the top left corners. If you click on the cells you will see a yellow exclamation mark (!) . Hovering over it will tell you the issue, but better still, click on the dropdown and you will see that one of the options is to ‘Convert to Number’.

If you click on this, all the cells you have highlighted will be converted to a number and you can use it.

numbers-seen-as-text-in-excel