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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:05:49  来源:igfitidea点击:

Convert numbers stored as text to numbers

excel-vbavbaexcel

提问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.

尝试声明mypriceCurrency.

回答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