vba 将存储为文本的数字转换为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1881240/
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
Convert numbers stored as text to numbers
提问by Tom
this code does not seem to work well always when copying currency data from another sheet:
从另一个工作表复制货币数据时，此代码似乎并不总是能正常工作：
Dim myprice As String
myprice = othersheet.Range("H" & c.Row).Value
ws.Range("C" & r).Value = myprice
ws.Range("C" & r).Style = "Currency"
sometimes cells have a warning that "this number is formatted as text"
有时单元格会出现“此数字格式为文本”的警告
回答by Lawrence P. Kelley
Excel's "Number Stored as Text" issue can be a bit vexing.
Excel 的“数字存储为文本”问题可能有点令人烦恼。
The Help recommendation is to perform a conversion operation by multiplying the value by 1. In practice, however, you're probably better off checking that myprice is numeric and then proceeding accordingly.
帮助建议是通过将值乘以 1 来执行转换操作。然而，在实践中，您最好检查 myprice 是否为数字，然后进行相应的操作。
For instance:
例如：
Dim myprice As String
myprice = othersheet.Range("H" & c.Row).Value
If IsNumeric(myprice) Then
myprice = CCur(myprice)
Else
'Catch non-numeric accordingly
myprice = "Couldn't convert to number"
End If
ws.Range("C" & r).Value = myprice
ws.Range("C" & r).Style = "Currency"
HTH
HTH
回答by SLaks
Try declaring mypriceas a Currency.
尝试声明myprice为Currency.
回答by Adriaan Stander
Have you tried using NumberFormat of the cell
您是否尝试过使用单元格的 NumberFormat
ws.Range("C" & r).NumberFormat = "$#,##0.00"
回答by Blink
This is working for me:
这对我有用：
For Each myprice In __your target range here__
myprice.Value = CDec(myprice.Value)
myprice.NumberFormat = "0.00" 'or whatever format you wish
Next myprice
回答by Peter
This code worked for me, to convert cells depending on their contents. It ran fairly quickly for a worksheet with >800,000 cells containing numbers stored as text, with 32-bit Excel 2013 running on a Windows 8.1 machine. It did the job in less than 2 minutes.
这段代码对我有用，可以根据内容转换单元格。对于包含以文本形式存储的数字的超过 800,000 个单元格的工作表，它运行得相当快，32 位 Excel 2013 在 Windows 8.1 机器上运行。它在不到 2 分钟的时间内完成了这项工作。
For Each aCell In ThisWorkbook.Sheets("Working data").UsedRange.Cells
If aCell.Errors.Item(xlNumberAsText).Value Then
aCell.Formula = aCell.Text
End If
Next aCell
