vba 如何将一些数字作为 Excel 中的文本转换为数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14100216/
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
How to convert some number as text in Excel into number?
提问by CodeLover
I just ran a Macro and got the below:
我刚刚运行了一个宏并得到了以下内容：
Sub Macro1()
'
' Macro1 Macro
'
'
Columns("B:B").Select
Range("B4").Activate
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "@"
End Sub
Now couldn't understand the difference between Selection.NumberFormat = "0.00"and Selection.NumberFormat = "@"Can you help me to understand the same?
现在弄不明白之间的差别Selection.NumberFormat = "0.00"和Selection.NumberFormat = "@"你能帮助我理解一样吗？
I was also trying to convert some number as text in Excel into number. What would be good syntax to do so?
我还试图将一些数字作为 Excel 中的文本转换为数字。这样做的好语法是什么？
Snapshot:
快照：
EDIT
编辑
I tried to convert all numbers which is stored as textto number by the below,but nothing changed. Please guide if I am wrong:
我试图将所有存储的数字转换为text下面的数字，但没有任何改变。如果我错了，请指导：
objSheet1.Columns(11).NumberFormat = "0"
回答by Siddharth Rout
Is this what you are trying?
这是你正在尝试的吗？
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long, i As Long
'~~> Change this to the relevant sheet name
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
'~~> Col B
.Columns(2).NumberFormat = "0.00"
'~~> Get the last row
lRow = .Range("B" & .Rows.Count).End(xlUp).Row
For i = 1 To lRow
.Range("B" & i).Formula = .Range("B" & i).Value
Next i
End With
End Sub
SCREENSHOT
截屏
回答by JMK
To convert a text stored as a number in a cell to a number, you could try something like this:
要将在单元格中存储为数字的文本转换为数字，您可以尝试如下操作：
Sub ConvertTextInCellToNumber(ByVal cellLocation As String)
Dim txt As String
txt = range(cellLocation).text
On Error GoTo Catch
Dim txtAsNumber As Double
txtAsNumber = txt
range(cellLocation).Value = txtAsNumber
Catch:
End Sub
