vba 防止逗号分隔的数字列表被解释为单个大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7525924/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Prevent comma-separated list of numbers being interpreted as single large value
提问by niko
33266500,332665100,332665200,332665300was the original value, cell should look like this: 33266500,332665100,332665200,332665300but what I see as the cell value in excel is 3.32665E+34
33266500,332665100,332665200,332665300是原始值，单元格应该是这样的：33266500,332665100,332665200,332665300但是我在 excel 中看到的单元格值是3.32665E+34
So the question is I want to convert it into the original string. I have found format function on google and I used it like these
所以问题是我想将其转换为原始字符串。我在谷歌上找到了格式函数，我像这样使用它
format(3.32665E+34,"standard")
giving it as 332,6650,033,266,510,000,000,000
给它作为 332,6650,033,266,510,000,000,000
How to parse it or get back the orginal string? I belive format is the function in vba.
如何解析它或取回原始字符串？我相信格式是 vba 中的函数。
回答by aevanko
Excel has a 15 digit precision limit. If the numbers are already shown like this when you access the file, there is no way to get the number back - you have already lost some digits. VBA code and formulas will not help you.
Excel 有 15 位精度限制。如果在访问文件时数字已经像这样显示，则无法取回数字 - 您已经丢失了一些数字。VBA 代码和公式不会帮助你。
If this is not the case, you can add a single quote ' mark before the number to store it as text. This will ensure Excel does not try to treat it as a number and thus lose precision.
如果不是这种情况，您可以在数字前添加单引号 ' 标记以将其存储为文本。这将确保 Excel 不会尝试将其视为数字并因此失去精度。
回答by David Schwartz
If you want the value kept exactly, store the data as a string, not as a number. The data type you are using simply doesn't have the ability to do what you are asking it to do.
如果您希望准确保留该值，请将数据存储为字符串，而不是数字。您使用的数据类型根本无法执行您要求它执行的操作。
回答by chris neilsen
The value needs to be entered into the cell as a string. You need to make whatever it is that inserts the value receed the value with a '.
该值需要作为字符串输入到单元格中。您需要使插入值的任何内容以'.
回答by Mike Woodhouse
If you're starting with an Excel file that has already been created then you've already lost the information: Excel has tried to understand what it was given and its best guess has turned out to be wrong. All you can do (if you can't get the source data) is go back to the creator of the Excel file and tell them what's wrong.
如果您从已经创建的 Excel 文件开始，那么您已经丢失了信息：Excel 试图了解它给出的内容，但它的最佳猜测结果证明是错误的。您所能做的（如果您无法获取源数据）就是返回 Excel 文件的创建者并告诉他们出了什么问题。
If you're starting with, say, a text file that you're importing, then the news is much better:
如果您从要导入的文本文件开始，那么消息会好得多：
If you're importing manually using the Text Import Wizard, then at "Step 3 of 3" you need to set "Column Data Format" for the problem field to "Text".
如果您使用文本导入向导手动导入，则在“第 3 步，共 3 步”中，您需要将问题字段的“列数据格式”设置为“文本”。
If you're using a macro, you'll need to specify a value for the TextFileColumnDataTypesproperty that does the same thing. The easiest way to get it right is to use the Macro Recorder.
如果您使用的是宏，则需要为TextFileColumnDataTypes执行相同操作的属性指定一个值。最简单的方法是使用宏记录器。
If you want the four values in the string to be separate cells, then again, look at the Text Import Wizard settings: in Step 1 of 3 you need to set "Delimited" data type (usually the default) and in Step 2 make sure that "Comma" is checked.
如果您希望字符串中的四个值是单独的单元格，那么再次查看文本导入向导设置：在第 1 步（共 3 步）中，您需要设置“分隔”数据类型（通常为默认值），并在第 2 步中确保检查“逗号”。
