VBA：将文本转换为数字

VBA: Convert Text to Number

提问by aLearningLady

I have columns of numbers that, for whatever reason, are formatted as text. This prevents me from using arithmetic functions such as the subtotal function. What is the best way to convert these "text numbers" to true numbers?

我有一列数字，无论出于何种原因，它们都被格式化为文本。这使我无法使用算术函数，例如小计函数。将这些“文本数字”转换为真实数字的最佳方法是什么？

Here is a screenshot of the specific issue:

以下是具体问题的截图：

I've tried these snippets to no avail:

我试过这些片段无济于事：

Columns(5).NumberFormat = "0"

and

和

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

回答by aLearningLady

Use the below function (changing [E:E]to the appropriate range for your needs) to circumvent this issue (or change to any other format such as "mm/dd/yyyy"):

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

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

P.S. In my experience, this VBA solution works SIGNIFICANTLY faster on large data sets and is less likely to crash Excel than using the 'warning box' method.

PS 根据我的经验，与使用“警告框”方法相比，此 VBA 解决方案在大型数据集上的运行速度明显更快，并且不太可能使 Excel 崩溃。

回答by BerticusMaximus

I had this problem earlier and this was my solution.

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

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

回答by Jones

This can be used to find all the numeric values (even those formatted as text) in a sheet and convert them to single (CSng function).

这可用于查找工作表中的所有数值（甚至那些格式化为文本的数值）并将它们转换为单个（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

回答by ConertVBA

This converts all text in columns of an Excel Workbook to numbers.

这会将 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

回答by Camilo Vargas

The solution that for me works is:

对我有用的解决方案是：

For Each xCell In Selection

  xCell.Value = CDec(xCell.Value)

Next xCell 

回答by Mikey

Using aLearningLady's answer above, you can make your selection range dynamic by looking for the last row with data in it instead of just selecting the entire column.

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

The below code worked for me.

下面的代码对我有用。

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

回答by Mahesh S

''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

回答by Louis

For large datasets a faster solution is required.

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

Making use of 'Text to Columns' functionality provides a fast solution.

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

Example based on column F, starting range at 25 to LastRow

基于 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