VBA EXCEL - NUMBERFORMAT 不起作用，但 FORMAT FUNCTION 起作用了
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18598304/
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
VBA EXCEL - NUMBERFORMAT didn′t work but FORMAT FUNCTION did
提问by Gera
I have a range of cells named MyCells as string type. In which there are strings with 26 digits.
我有一系列名为 MyCells 的单元格作为字符串类型。其中有26位数字的字符串。
Why the first statement (below), with NumberFormat didn′t work, but the second did?
为什么第一个语句（下面），使用 NumberFormat 不起作用，但第二个语句起作用了？
Trying cells look like this:
尝试单元格看起来像这样：
38935094000163001000052234 --> 38.935.094/0001-63 001 000052234
38935094000163001000052234 --> 38.935.094/0001-63 001 000052234
First attempt (didn′t work):
第一次尝试（没有用）：
range(MyCells).Select
Selection.NumberFormat = "00"".""000"".""000""/""0000""-""00"" ""000"" ""000000000"
Second (successful):
第二个（成功）：
range(MyCells).Select
For Each c In Selection
c = Format(c, "00"".""000"".""000""/""0000""-""00"" ""000"" ""000000000")
Next
回答by pnuts
"The Format function uses different format code strings than do the NumberFormat and NumberFormatLocal properties." from http://msdn.microsoft.com/en-us/library/office/aa224873%28v=office.11%29.aspx.
“Format 函数使用与 NumberFormat 和 NumberFormatLocal 属性不同的格式代码字符串。” 来自http://msdn.microsoft.com/en-us/library/office/aa224873%28v=office.11%29.aspx。
回答by tigeravatar
Excel only keeps the first 15 digits of numbers. That number you put up is 26 digits and the only way Excel would keep the numbers after the first 15 digits would be if it was stored as a text. As a result, number formats won't work on it because Excel sees it as a text string, not a number.
Excel 只保留数字的前 15 位。您输入的数字是 26 位数字，Excel 保留前 15 位数字后的唯一方法是将其存储为文本。因此，数字格式对它不起作用，因为 Excel 将其视为文本字符串，而不是数字。
