Ask Your Question
1

how convert from text to numbers after importing [closed]

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

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
2

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

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
2

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

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

Comments

Thank you!

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

Question Tools

1 follower

Stats

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

Seen: 74 times

Last updated: Feb 22