Excel converts string in CSV file as number
 

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.

Excel converts string in CSV file as number
 

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.
--
Kevin Backmann


"DavidC" wrote:

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.

Excel converts string in CSV file as number
 

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

On Jul 25, 2:16*pm, DavidC wrote:
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.

Excel converts string in CSV file as number
 

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?

"DavidC" wrote:

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.

Excel converts string in CSV file as number
 

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.



On Jul 25, 9:16*am, DavidC wrote:
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.

Excel converts string in CSV file as number
 

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

"dlw" wrote:

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?

"DavidC" wrote:

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.