How convert from text to numbers after importing

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.