在 Excel VBA 中将存储为文本的数字转换为数字的最简单方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33952013/
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
Easiest way to convert number stored as text to number in Excel VBA
提问by OlaOkland
I trying to convert this text into numbers:
我试图将此文本转换为数字：
I have tried different formulas but Excel is not recognizing the format. However, using the code below it converts only the first three numbers and removes the rest.
我尝试了不同的公式，但 Excel 无法识别格式。但是，使用下面的代码只转换前三个数字并删除其余数字。
Sub ConvertTextNumberToNumber()
For Each WS In Sheets
On Error Resume Next
For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(r) Then r.Value = Val(r.Value)
Next r
Next WS
End Sub
The result looks like this
结果看起来像这样
Does anyone have an ease fix for this without removing any numbers?
有没有人可以轻松解决此问题而不删除任何数字？
采纳答案by EEM
Providing this answer in relation with OP statement:
提供与 OP 声明相关的答案：
I have tried different formulas but Excel is not recognizing the format.
我尝试了不同的公式，但 Excel 无法识别格式。
There is no need to use VBA for that operation.
该操作无需使用 VBA。
Use this formula if your decimal character is a period [.]
如果您的十进制字符是句点 [.]，请使用此公式
=SUM( SUBSTITUTE( SUBSTITUTE( B2, " ", "" ), ",", "." ) )
Or this one if your decimal character is a colon [,]
或者如果您的十进制字符是冒号 [,]
=SUM( SUBSTITUTE( B2, " ", "" ) )
回答by R3uK
Easiest way to convert a number stored as text, is to multiply it by 1,
So try this If IsNumeric(r) Then r.Value = (r.Value)*1
Or you can copy paste with a multiplication by 1 and this works too! ;)
转换存储为文本的数字的最简单方法是将其乘以1，
所以试试这个If IsNumeric(r) Then r.Value = (r.Value)*1
或者您可以复制粘贴并乘以 1，这也有效！;)
Sub ConvertTextNumberToNumber()
For Each WS In Sheets
On Error Resume Next
For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(r) Then r.Value = (r.Value)*1
''Or test the values in the next column on the right
'If IsNumeric(r) Then r.Offset(0,1).Value = (r.Value)*1
Next r
Next WS
End Sub
回答by Jean-Pierre Oosthuizen
Provided your data is always using a "space" as a thousand separator and a "comma" as a decimal point, you can use the following(Modified from your original script).
如果您的数据始终使用“空格”作为千位分隔符并使用“逗号”作为小数点，您可以使用以下内容（从您的原始脚本修改）。
Sub ConvertTextNumberToNumber()
For Each WS In Sheets
On Error Resume Next
For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
'For testing purposes I like to see the cell which is selected
r.Select
'Setting the Cell Number Format
'for more or less decimals just add/remove 0 after the .
r.NumberFormat = "0.00"
'Assigning the Cell value to a String
String1 = r.Value
'Replacing the spaces with no space
String1 = Replace(String1, " ", "")
'Replacing the comma with a point
String1 = Replace(String1, ",", ".")
'Putting the new value into the cell
r.Value = String1
Next r
Next WS
End Sub
