Displaying Numbers as Text in CSV

Posted by Mamet on December 31, 2001 8:19 AM

have an ASP page that downloads a CSV file of data.
In this data are some IDs that should be treated as Text but, when the file is opened in Excel, they are treated as numbers. Leading zeroes are dropped.

I have tried to enclose the IDs in double quotes, but the leading zeroes are still dropped.

I have tried starting each field with a single quote, in an attempt to alert Excel that the field is text, but the single quote appears in the cell with the actual value of the ID. That is unacceptable.

Is there any way to "trick" Excel into treating a numeric field as text - without displaying unwanted characters in the cell?

Thanks for your time,

Posted by Scott on December 31, 2001 8:36 AM

If you bring in the text with a single quote in front of it, and then edit each cell by hitting F2, and then Enter, it will leave you with what you want. Not a perfect solution, but you could create a macro to do this for you in no time.

Posted by Troy on December 31, 2001 8:38 AM

When you open the file and select to open a comma delimited file, the next step lets you define each field. You should be able to treat the column as text (it will default to "general").

Posted by Mamet on December 31, 2001 9:30 AM

When i open the file as a CSV, excel automatically opens it, it does not bring me to the define fields wizard. is there a setting that i can over-ride?

Posted by IML on December 31, 2001 9:37 AM

Formula Band Aid

In case you can't figure it out correctly, you could insert a column and a formula such as
=REPT(0,8-LEN(A1))&A1 or
=TEXT(A1,"00000000") for 8 zeros and copy it down. copy, paste special values and delete the orignal.

Good luck