Stop Ms Excel automatically formatting numeric strings as numbers

Question

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

One of the columns has a character string, which is numeric for some strings, for example. "05-0880".

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

I assume this is because Excel is smart and decided that “05-0808” represents a time value or date of some type and converts the string to the 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 underlying the query (exporting the Datasheet), this conversion will not happen. (Assuming that access to the output in Excel format should prevent conversion).

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

Is there a way to use the format () function to force the output of a string? If so, will it help when Excel can still do its smart conversion?

Just using a basic query, since the output is an option, but not perfect, because formatting and grouping are lost. Exporting a report to text format is a disaster because the columns are not split.

Answer 1

Actually, this is more like a different path. MS Access is too dumb to properly export it from a report. You can reserve data with quotation marks or apostrophes; however, as you know, this is pretty ugly and requires further processing.

My way around this was to carefully process the query and then export the query results, not the report. This will give you the expected results.

Answer 2

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

Answer 3

make sure you use a single quote and not another apostrophe.