Excel converts string in CSV file as number

[DavidC]

A file named test.csv has one line in it:

16E035

This is an accounting code used at our university. If I open that file,
Excel shows 1.6*10^36 in cell A1. If I specify that the value is a string:

"16E035"

Excel still converts it to 1.6*10^36. How should this value be entered in
the file so Excel leaves it as a character string?

Thanks for your help.

 

[Kevin B]

From Excel, click FILE, OPEN change the FILES OF TYPE to text and select the
file. This opens the import wizard where you specify what the delimiter is
and what data type to assign to each field. For an accounting code such as
yours Excel will set the column to a GENERAL data type which will treat the
value as a number. Change the data type to text and your data will import as
a lable and not as a value.

 

[Pete_UK]

If you include another character, like:

'16E035, or _16E035

it will be treated as text. The first one has the advantage that it
will not be displayed.

Alternatively, rename the file as a .txt file, then do File | Open in
Excel and you will be taken into the Data Import Wizard, in the third
panel of which you can specify that you want the data to be imported
as text.

Hope this helps.

Pete

 

[dlw]

when I tried it with "16E035" in the csv file, and when opening it in excel
chose " as the text qualifier and text as the field type, it worked.
Then I tried it with just 16E035 in the csv file, and chose text as the
field type, and that worked too.

Not sure what you are doing wrong?

 

[Tim879]

Instead of opening the CSV file, open a blank spreadsheet.

Go to Data -> Import

Select the CSV file, hit next a few times...on the 3rd step, select
the column that has the accounting code in it and format it as text.

 

[dlw]

ay, there's the rub, my test file was .txt so it went into the import
routine...