vba Excel 数字格式。将我的单元格视为数字，但不要管它的格式！（即尾随 0）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6495036/
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
Excel number formatting. Treat my cell as a number but leave its formatting alone! (ie Trailing 0's)
提问by Doug S.
I have (what seems like it should be) a simple problem. I need to be able to tell excel (in vba) that a cell's contents are numeric, but I don't really want to apply any formatting to it. I need my trailing zeros left how they are. Am I missing something incredibly simple?
我有（看起来应该是）一个简单的问题。我需要能够告诉 excel（在 vba 中）单元格的内容是数字，但我真的不想对其应用任何格式。我需要我的尾随零保持原样。我错过了一些非常简单的东西吗？
Update:
更新：
I'm getting xml data from a query and am placing it into the spreadsheets. But lets say one of the cells has 589.950000I need to keep those additional zeros on display for the user (don't ask me why, they just want the precision) but excel converts it to 589.95. I also need for them to be able to do spreadsheet calculations on that cell (so I can't format it as text).
我正在从查询中获取 xml 数据并将其放入电子表格中。但是可以说其中一个单元格589.950000我需要为用户保留这些额外的零（不要问我为什么，他们只想要精度）但 excel 将其转换为589.95. 我还需要他们能够对该单元格进行电子表格计算（因此我无法将其格式化为文本）。
Update 2:
更新 2：
Further Clarification. Lets say I have 4 values that I'm going to place into a column
进一步澄清。假设我有 4 个值要放入一列
595.56000
15.00
90.00050
1919.120000000
is there one numeric format that I can apply to that column that will display those exact numbers?
是否有一种数字格式可以应用于该列来显示这些确切数字？
采纳答案by Lance Roberts
You can't do this with one custom format.
你不能用一种自定义格式来做到这一点。
What you can do is make a macro that does the input for you and modifies each cell format as it puts the value into it. You can use the Macro Recorder to get a handle on where to start with this.
您可以做的是制作一个宏，为您执行输入并在将值放入其中时修改每个单元格格式。您可以使用宏记录器来了解从哪里开始。
回答by Jeezy
Can't believe I just stumbled on this...
不敢相信我只是偶然发现了这个......
I know it's old but might as well give very simple answer:
我知道它很旧，但不妨给出非常简单的答案：
Custom format and simply use @ symbol. Will be treated as integer and left exactly as typed in to cell.
自定义格式，只需使用@ 符号。将被视为整数并完全按照输入到单元格的方式保留。
回答by JMax
You mean you want to use the value of the cell as a numerical one but you don't want to change the real val of the cell ?
您的意思是您想将单元格的值用作数字值，但又不想更改单元格的真实值？
In vba, you can use CInt(yourvar)to convert a string to number. Thus, the value you get will sure be an integer.
在 vba 中，您可以使用CInt(yourvar)将字符串转换为数字。因此，您得到的值肯定是一个整数。
[edit]Btw, if you want to display or set back the value to a cell with the format you want, you can use the Format ( expression, [ format ] )function of Excel vba
[编辑]顺便说一句，如果要将值显示或设置回您想要的格式的单元格，您可以使用Format ( expression, [ format ] )Excel vba的功能
[edit 2]
[编辑 2]
As you cannot predict how many zeros you will have, i can't see any number format that would work. You'd probably better use the Text formatas Lance Robert suggested. To use formula on text cells, you can use the VALUEformula to convert the cell to use it as a number. If you need it on a range, you may have to use array formulaslike this : {=SUM(IF(ISTEXT(A1:A4);VALUE(A1:A4);A1:A4))}.
由于您无法预测您将拥有多少个零，因此我看不到任何可行的数字格式。您可能最好使用Lance Robert 建议的文本格式。要在文本单元格上使用公式，您可以使用VALUE公式来转换单元格以将其用作数字。如果您需要在一个范围内使用它，您可能必须使用这样的数组公式: {=SUM(IF(ISTEXT(A1:A4);VALUE(A1:A4);A1:A4))}。
Please let us know if you want some help on these formulas.
如果您需要有关这些公式的帮助，请告诉我们。
Regards,
问候，
Max
最大限度
回答by JDunkerley
I think you can do:
我认为你可以这样做：
Val(Range("A1").Value)
Which will evalulate the text to a number
这将评估文本为一个数字
回答by Excellll
If you know the number of decimal places you need, you can use something like the following:
如果您知道所需的小数位数，则可以使用以下内容：
Range("A1").NumberFormat = "0.000000"
I suppose you could even get fancy and check the number of trailing 0s in the code and adjust the formatting as required.
我想您甚至可以花哨并检查代码中尾随 0 的数量并根据需要调整格式。
UPDATE:
更新：
Here's a VBA function that takes the number as a string and returns the appropriate NumberFormat string.
这是一个 VBA 函数，它将数字作为字符串并返回适当的 NumberFormat 字符串。
Private Function trailing(strNum As String) As String
'From number entered as string, returns Excel Number format to preserve trailing zeroes in decimal.
Dim decpt As Integer
Dim aftdec As Integer
Dim strTmp As String
decpt = InStr(strNum, ".")
If decpt = 0 Then
strTmp = "0"
Else
aftdec = Len(strNum) - decpt
strTmp = "0."
If aftdec <> 0 Then
For i = 1 To aftdec
strTmp = strTmp & "0"
Next
End If
End If
trailing = strTmp
End Function
