stop Ms Excel auto-formatting numeric strings as numbers

Question

I am exporting a report from MS Access(2003) to Excel (97-2003) output.

One of the columns has a character string that is numeric for some rows e.g. "05-0880".

When I open the output file in MS Excel the corresponding cell is set to the number -372424.

I assume this is caused by Excel being "clever" and deciding that "05-0808" represents a time value or date of some sort and converting the string to a corresponding numeric value.

In my case the data represents product codes and this conversion is very undesirable. If I export the output of the report's underlying query (exporting a Datasheet) this conversion does not happen. (Suggesting that it must be possible for Access to output to Excel format in a way that prevents conversion).

I tried changing the format attribute of the field in the report, but there is no "Text" option, and "Standard" seems to behave the same as leaving it blank.

Is there a way to use the format() function to force string output? If so, will this even help when Excel may still do its clever conversion?

Just using the underlying query as output is an option but not ideal because formatting and grouping are lost. Exporting a report to a text format is a disaster because the columns are not delimited.

Answer 1

Export "'05-0880" instead of "05-0880".

Answer 2

Actually, it is more like the other way around. MS Access is too dumb to export it properly from a report. You can armor the data with quotes or apostrophes; however, as you know, that's quite ugly and requires post processing.

My way around it was to carefully craft a query and then export the results of the query rather than the report. This will give you the results you expect.