VBA：将文本转换为数字

Question

我有一列数字，但由于某种原因被格式化为文本，这导致我无法使用像小计函数这样的算术函数。如何最好地将这些“文本数字”转换为真正的数字？

以下是特定问题的截图：

我尝试了以下代码片段，但未成功：

Columns(5).NumberFormat = "0"

和

 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

- aLearningLady

---

嗨，斯科特 - 我同意这是一个好的解决方案。然而，我现在可以听到最终用户抱怨“一切都加起来等于零！”。由于VBA已经被用来运行脚本（我应该在问题中提到这一点），我认为最好的方法是在第一时间包含代码以避免这种对话。 - aLearningLady

使用CInt(Cell().Value)将单元格的值转换为数字。 - gizlmo

我曾经遇到过类似的问题，即货币值由BRL中的R$ 0,00组成，而CCUR()函数真的解决了我的难题。我认为Cdec()函数也会有所帮助。请查看链接中的其他函数。如何使用CDEC函数（VBA） - Anderson Wanselowski

我知道已经有一段时间了，但你输入值的方式可能不正确。 如果你这样做： " cell.Value = "865" " ，单元格会被填入文本而不是数字。 如果你这样做 " cell.value = 865 "，单元格会被填入数字。 - smirnoff103

Answer 1

使用以下函数（将[E:E]更改为您的需求的适当范围），以避免此问题（或更改为任何其他格式，例如“mm/dd/yyyy”）：

使用下面的函数（将[E:E]替换为您需要的适当范围），即可解决此问题（或更改为任何其他格式，如“mm/dd/yyyy”）:

[E:E].Select
With Selection
    .NumberFormat = "General"
    .Value = .Value
End With

顺便说一下，根据我的经验，这个VBA解决方案在处理大数据集时工作速度明显更快，并且比使用“警告框”方法更不容易导致Excel崩溃。

- aLearningLady

---

在某些工作表上，这填充了整个列，而它最初只包含200行。TextToColumn方法似乎更可靠。 - iDevlop

谢谢，我修改了代码，不再使用选择，而是像这样使用：mywb.sheets(1).range("E:E")。现在可以正常工作了。 - mx5_craig

为了避免整列问题，请添加以下代码：var = ActiveSheet.UsedRange.Rows.Count Range("E1:E" & var).Select在以下代码之前：Selection.Value = .Value - Jason R. Escamilla

Answer 2

这可以用来查找表格中所有数值（即使是以文本格式呈现的）并将它们转换为单精度浮点数（使用CSng函数）。

For Each r In Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeConstants)
    If IsNumeric(r) Then
       r.Value = CSng(r.Value)
       r.NumberFormat = "0.00"
    End If
Next

- Jones

---

以前，我必须删除附加到数字的“B”（十亿）。这个解决方案有效，但我使用了CDec，因为CSng会将1.45转换为1.45000023，例如。 - xm1

Answer 3

我之前也遇到过这个问题，以下是我的解决方案。

With Worksheets("Sheet1").Columns(5)
    .NumberFormat = "0"
    .Value = .Value
End With

- BerticusMaximus

Answer 4

这将把Excel工作簿中列中的所有文本转换为数字。

Sub ConvertTextToNumbers()
Dim wBook As Workbook
Dim LastRow As Long, LastCol As Long
Dim Rangetemp As Range

'Enter here the path of your workbook
Set wBook = Workbooks.Open("yourWorkbook")
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For c = 1 To LastCol
Set Rangetemp = Cells(c).EntireColumn
Rangetemp.TextToColumns DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
Next c
End Sub

- ConertVBA

---

TextTo列是最好的方法。在一些工作表上，我用.Value = .Value方法出现了问题（整个列被填充，而不是200行）。 - iDevlop

Answer 5

我多次使用的解决方案是：

Sub ConvertTextToNumber()

With Range("A1:CX500") 'you can change the range 
.NumberFormat = "General"
.Value = .Value 
End With

End Sub

- Hakob TARPOSHYAN

Answer 6

''Convert text to Number with ZERO Digits and Number convert ZERO Digits  

Sub ZERO_DIGIT()
 On Error Resume Next
 Dim rSelection As Range
 Set rSelection = rSelection
 rSelection.Select
 With Selection
  Selection.NumberFormat = "General"
  .Value = .Value
 End With
 rSelection.Select
  Selection.NumberFormat = "0"
 Set rSelection = Nothing
End Sub

''Convert text to Number with TWO Digits and Number convert TWO Digits  

Sub TWO_DIGIT()
 On Error Resume Next
 Dim rSelection As Range
 Set rSelection = rSelection
 rSelection.Select
 With Selection
  Selection.NumberFormat = "General"
  .Value = .Value
 End With
 rSelection.Select
  Selection.NumberFormat = "0.00"
 Set rSelection = Nothing
End Sub

''Convert text to Number with SIX Digits and Number convert SIX Digits  


Sub SIX_DIGIT()
 On Error Resume Next
 Dim rSelection As Range
 Set rSelection = rSelection
 rSelection.Select
 With Selection
  Selection.NumberFormat = "General"
  .Value = .Value
 End With
 rSelection.Select
  Selection.NumberFormat = "0.000000"
 Set rSelection = Nothing
End Sub

- Mahesh S

Answer 7

使用aLearningLady上面的答案，您可以通过查找最后一行数据而不是仅选择整个列来使您的选择范围动态化。

以下代码对我起作用。

Dim lastrow as Integer

lastrow = Cells(Rows.Count, 2).End(xlUp).Row

Range("C2:C" & lastrow).Select
With Selection
    .NumberFormat = "General"
    .Value = .Value
End With

- Mikey

Answer 8

我有效的解决方法是：

对我来说适用的解决方案是：

For Each xCell In Selection

  xCell.Value = CDec(xCell.Value)

Next xCell 

- Camilo Vargas

Answer 9

对于大型数据集，需要更快的解决方案。

利用“文本到列”功能提供了快速的解决方案。

以F列为例，起始范围为25到LastRow。

Sub ConvTxt2Nr()

Dim SelectR As Range
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Sheets("DumpDB")

LastRow = sht.Cells(sht.Rows.Count, "F").End(xlUp).Row

Set SelectR = ThisWorkbook.Sheets("DumpDB").Range("F25:F" & LastRow)

SelectR.TextToColumns Destination:=Range("F25"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

End Sub

- Louis

---

虽然“文本分列”是一种不错的方法，但你只能在一个列上使用它。如果你尝试使用“文本分列”解决方案，那么你应该一次只在一个列上使用它。 - sander126

Answer 10

从记录的宏中得到以下代码；对于新应用程序，您只需要更新选择和范围：

Sub num()

Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
End Sub

- A Albuquerque