在 VBA 中将 Excel 中的文本转换为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7165949/
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 text to number in Excel in VBA
提问by Bruno
For some reason Excel is converting my number into text and adding a preceding apostrophe in every cell in column E3 and F3.
出于某种原因，Excel 正在将我的数字转换为文本，并在 E3 和 F3 列的每个单元格中添加前面的撇号。
I need to convert columns E3:F3 back to numbers and format them to currency. How do I do that?
我需要将列 E3:F3 转换回数字并将它们格式化为货币。我怎么做？
A1:K2 is the header.
A1:K2 是标题。
The code below is not working:
下面的代码不起作用：
Set wb = objApp.Workbooks.Open("aFile.xls", True, False)
wb.Sheets(1).Rows(2).Delete
wb.Sheets(1).Range("E3:F3") = Mid(wb.Sheets(1).Range("E3:F3"), 2,
Len(wb.Sheets(1).Range("E3:F3")) - 2)
wb.Sheets(1).Range("E3:F3").NumberFormat = "$#,##0.00"
采纳答案by BradC
Assuming you want the same currency formatting you get from the toolbar, this works:
假设您想要从工具栏中获得相同的货币格式，这有效：
wb.Sheets(1).Range("E3:F3").Formula = wb.Sheets(1).Range("E3:F3").Value
wb.Sheets(1).Range("E3:F3").Style = "Currency"
Just using worksheet.Range()with no properties forces Excel to guess exactly which property you actually mean (this is called the "default property"), and you get inconsistent results.
仅使用worksheet.Range()不带属性会强制 Excel 准确猜测您实际指的是哪个属性（这称为“默认属性”），并且您会得到不一致的结果。
回答by WireTalents
If your text is only a number, the answer is simple. Multiply it by 1.
Say cell A1= '00001234 or a formula or macro returns a piece of text, multiply the answer by 1.
"00001234" * 1 = 1234.
I want to extract the value of a Label or a txtBox on a VBA form.
Num = lblText * 1
Another example:
If .txtLevel10 * 1 > 50 Then...etc.
Also works with some other data types "16-Jan-15" *1 = 16/1/15
Works in Excel & VBA but only if there are no other characters in the original text.
Cheers
如果您的文本只是一个数字，那么答案很简单。乘以 1。
假设单元格 A1= '00001234 或公式或宏返回一段文本，将答案乘以 1。
“00001234” * 1 = 1234。
我想提取 Label 或 txtBox 上的值VBA 表格。
Num = lblText * 1
另一个例子：
If .txtLevel10 * 1 > 50 Then...等。
也适用于其他一些数据类型 "16-Jan-15" *1 = 16/1/15
适用于 Excel 和 VBA，但前提是原始文本中没有其他字符。干杯
回答by James
Try:
尝试：
Range("E3:F3").Style = "Currency"
回答by transistor1
Try highlighting that column and doing Data->Text To Columns (Excel 2003; in 2007+ Text to columns is on one of the ribbons). Click 'Delimited', then 'Next', Next again, then select General as the format. This should convert the whole column into a number.
尝试突出显示该列并执行数据->文本到列（Excel 2003；在 2007+ 中，文本到列位于其中一个功能区上）。单击“分隔”，然后单击“下一步”，再次单击“下一步”，然后选择“常规”作为格式。这应该将整个列转换为一个数字。
If this works, it is easily automated. Let me know if you need code.
如果这有效，它很容易实现自动化。如果您需要代码，请告诉我。
EDIT - you have to do one column at a time.
编辑 - 你必须一次做一列。
回答by Igor Turman
This, actually, works. The key is to apply format beforesetting the value:
这实际上是有效的。关键是在设置值之前应用格式：
Set wb = objApp.Workbooks.Open("aFile.xls", True, False)
wb.Sheets(1).Rows(2).Delete
wb.Sheets(1).Range("E3:F3").NumberFormat = "$#,##0.00"
For Row = 3 To 3 'your rows range in case you need iterate through (1 row only in your case)
For Column = 5 To 6 'E3:F3
wb.Sheets(1).Cells(Row, Column) = Mid(wb.Sheets(1).Cells(Row, Column), 2, Len(wb.Sheets(1).Cells(Row, Column)) - 2)
Next Column
Next Row
回答by Tim Williams
Len(wb.Sheets(1).Range("E3:F3"))
For me this just (as expected) throws an error. You'll have to process each cell individually to use your approach.
对我来说，这（正如预期的那样）会引发错误。您必须单独处理每个单元格才能使用您的方法。
Dim c as Range
Set wb = objApp.Workbooks.Open("aFile.xls", True, False)
With wb.Sheets(1)
.Rows(2).Delete
For each c in .Range("E3:F3").cells
c.Value = Mid(c.value, 2, Len(c.value)-2)
c.NumberFormat = "$#,##0.00"
next c
End With
