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
Table of contents
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.
This should work most of the time. However, occasionally this is not enough
Using the InBuilt Conversion Tool
Another way to convert numbers seen as text in Excel 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.
Related
IF function not working with numbers
How to get data from PDF to Excel
Remove duplicates NOT WORKING if number formats different
Correcting numbers in Excel with incorrect commas, points or spaces