After working with an imported file, I noticed that the numbers are being treated as text.
The numbers in some of the columns are preceded by a single quote (" ’ "). These columns can be readily converted to numbers using Data–Text to Columns.
Single quotes do not precede the numbers in other columns and I am at a loss as to how to format those columns as numbers.
I would appreciate any help.
Thank you.
Check for a leading or trailing white space around the mystery numbers that are treated as text.
Ctrl-H will get you find and replace. Lose the spaces.
e.g. space-5-space is then converted to quote-5 and you are bacxk where you know how to fix it.
Thank you!
I agree this is a problem.
How to tell if a number is currently coded as Text: select a cell and look at the Sum= box. If this shows zero then the number is coded in text format.
Create a column with formula =VALUE(A2) and copy this downwards.
Alternatively, there should be a way to run a macro on the original column that uses VALUE() to change these but I’ve not tested it.
Note that if the column will be used as the Array parameter of a VLOOKUP the type of the value in the SearchCriterion parameter must match those in the Array.
VALUE(othercolumnNN) leaves the cell value as a formula. To then convert the cells to simple numbers, select them and choose menu Data > Calculate > Formula to Value.
You can also use menu Data > Text to Columns… to convert a column in-place, see question 1843.