Why can't I format digits as text?

[RobG]

I have a CSV file with a column of product codes of the form:
9311662126351. When I open the file in Excel, it formats the cells as
numbers and displays them like: 9.31166E+12.

I have selected the column and formatted the cells as text, however
despite the help text telling me that "...cells are treated as text
even when there's a number in the cell", Excel refuses to display them
as text and keeps the scientific notation.

This is really annoying, is there a work around for this bug?

 

[Peo Sjoblom]

Format as number with zero decimals as long as you don't use more than 15
digits or import the csv file and select text under column data format


--


Regards,


Peo Sjoblom

 

[RobG]

Format as number with zero decimals as long as you don't use more than  15
digits

Thanks for the speedy reply Peo, however I don't want them as numbers
as I don't know how big the product numbers will get and don't want
any system imposed constraints.

or import the csv file and select text under column data format

Painful, but it works. Why doesn't the format tool work the way it's
designed?

 

[Ron Rosenfeld]

I have a CSV file with a column of product codes of the form:
9311662126351. When I open the file in Excel, it formats the cells as
numbers and displays them like: 9.31166E+12.

I have selected the column and formatted the cells as text, however
despite the help text telling me that "...cells are treated as text
even when there's a number in the cell", Excel refuses to display them
as text and keeps the scientific notation.

This is really annoying, is there a work around for this bug?

Since you mentioned you don't know how long the product numbers might get, this
should work for you:

Change the extension on the file from .csv to .txt

When you open it with Excel, you will be taken to the Text Import Wizard.

At step 3 (I believe), you will be given the option of formatting the
appropriate column as text.
--ron

 

[ShaneDevenshire]

Hi,

If you've never use the Import Wizard, it is as Ron says the 3rd step of the
wizard. At that step you pick the field you want to be text in the Data
Preview area and then you click the Text option button in the top half of the
screen. You can do this for as many fields as you want.

Note if you exceed 15 digit in the .csv file format Excel will replace all
the additional digits with 0's. The scientific notation isn't really a
problem, but loosing the extra digits is. When you bring large numbers into
Excel they are put in scientific notation for viewing purposes, all you would
need to do is change the format.

If for any reason you can't get the file type change to TXT to work, you can
use Access to import the file as a csv file, the Access import Wizard will
ask you what type of field you want - it will pick long integer, but you can
override that as text. Once in Access, the is a one click command to send
the table to Excel and it will come in as a text field.