停止Ms Excel将数字字符串自动格式化为数字 [英] stop Ms Excel auto-formatting numeric strings as numbers

问题描述

我从MS Access（2003）输出报告到Excel（97-2003）输出。

其中一列的字符串是数字一些行例如05-0880。

当我在MS Excel中打开输出文件时，相应的单元格被设置为数字 -372424 。

我认为这是由于Excel是聪明的，并确定05-0808代表某种类型的时间值或日期，并将字符串转换为相应的数值。 b
$ b

在我的情况下，数据代表产品代码，这种转换是非常不可取的。
如果我导出报表的基础查询（导出数据表）的输出，则不会发生此转换。 （建议访问必须以阻止转换的方式输出为Excel格式）。

我试着改变报表中字段的格式属性，但没有文本选项，而标准似乎表现为空白。

有没有办法使用 format（）函数强制输出字符串？
如果是这样，这将甚至帮助Excel时，仍然可以做它巧妙的转换？

只使用基础查询作为输出是一个选项，但不是理想的，因为格式和分组丢失。将报表导出为文本格式是一种灾难，因为列没有分隔。

方式。 MS Access太笨了，无法从报告中正确导出。你可以使用引号或撇号装饰数据;然而，如你所知，这是相当丑陋的，需要后处理。

我的方法是仔细制作一个查询，然后导出查询的结果，而不是报告。这会给你预期的结果。

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.

解决方案

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.

这篇关于停止Ms Excel将数字字符串自动格式化为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！