When importing data from external sources you may find that your number values unexpectedly import as text.
It’s usually obvious when this happens – the numbers are left-aligned.
The cells may look like a number, but Excel thinks that they are text.
You’ll find that you can’t perform calculations against “text-numbers”. For the picture below, if I used the formula =SUM(A1:A10) then the result would be 0.
Excel 2002 (Excel XP) made some advances in this area by way of automatic error-checking (aka the Green Triangle).
You can quickly convert the cell to a proper number by highlighting your list of misbehaving numbers, click the exclaimation mark and choose ‘Convert to Number’ from the dropdown.
For those of you running a version of Excel less than 2002, the trick I use goes as follows:
1. Copy a Blank Cell (or a cell containing the number 0)
2. Select your list of text-numbers
3. Choose Paste Special from the Edit menu.
4. Paste=Values, Operation=Add
By applying a math operation on the text-numbers, the result is a number!
Sure beats pressing “F2 Enter” 100 times like I used to.