Ask Your Question

how convert from text to numbers after importing [closed]

asked 2017-02-08 05:05:01 +0100

catbill gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by catbill
close date 2017-09-10 18:36:20.278566

2 Answers

Sort by » oldest newest most voted

answered 2017-02-22 14:52:27 +0100

Taffel gravatar image

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.

edit flag offensive delete link more


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.

skierpage gravatar imageskierpage ( 2018-10-15 06:57:53 +0100 )edit

answered 2017-02-08 16:34:52 +0100

Bob Dowling gravatar image

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.

edit flag offensive delete link more


Thank you!

catbill gravatar imagecatbill ( 2017-03-10 20:59:42 +0100 )edit

Question Tools

1 follower


Asked: 2017-02-08 05:05:01 +0100

Seen: 8,378 times

Last updated: Feb 22 '17