Forcing String Format for Excel

[scking]

I have columns of data that are mixed number/text that should ALL be formatted as text. Excel tries to help me by formatting each cell according to the value. I've created a macro that places a ' prior to all cell values in a selected range. This works well in most cases but for some reason not always. My second problem is similar, a text value consisting of a figure and index is show like 10/1 and Excel always changes the format to a date. How can I force Excel to recognize these values as string values without using the rather brute force method of prepending a ' to the cell? My question is about either Excel or ASP as I extract the data from a database using ASP and send it to the user as an Excel spreadsheet.




-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[PeterMoran]

Hi,

I don't know ASP so am not much help there.

With Excel it seems to mee that if you import data into Excel from a TXT file you have the ability to set the format of your problem columns to TEXT rather than General, which I think is the default.

If you paste data into Excel it is advantageous to try and set the formatting of your problem columns to TEXT before you Paste, or maybe Paste Special Values.

Also you might try to Export your data from the database as a CSV file, or if it is a TXT file, rename it to a CSV file. Excel opens a CSV file without any intermediate processing like you get with a TXT file. You may find one better than the other.

Hope this is of some help!

Regards,

Peter Moran
Two heads are always better than one!