vba 将存储为文本的数字转换为数字？

Convert numbers stored as text to numbers?

提问by user1283776

How can I convert numbers stored as text to numbers?

如何将存储为文本的数字转换为数字？

I have tried setting:

我试过设置：

ActiveSheet.Range("H154").NumberFormat = "General"

But it doesn't work!

但它不起作用！

The only things I've found that work are using "Text to columns" or clicking the cell to edit it and then clicking Enter.

我发现唯一可行的方法是使用“文本到列”或单击单元格进行编辑，然后单击 Enter。

But I would really like to find a way to turn number cells in a sheet stored as text into numbers using VBA.

但我真的很想找到一种方法来使用 VBA 将存储为文本的工作表中的数字单元格转换为数字。

回答by chris neilsen

A general technique is to Copy PasteSpecial, Multiply by 1

一般技术是复制 PasteSpecial，乘以 1

In code, something like this:

在代码中，是这样的：

Sub ConvertToNumber()
    Dim rng As Range
    Dim cl As Range
    Dim rConst As Range

    ' pick an unused cell
    Set rConst = Cells(1, 4)
    rConst = 1

    Set rng = Cells.SpecialCells(xlCellTypeConstants)
    rng.NumberFormat = "General"
    rConst.Copy
    rng.PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply

    rConst.Clear
End Sub

回答by Kris Walsh

I'm not a coding expert and the "Number Stored as Text" error plagued me for a long time.

我不是编码专家，“数字存储为文本”错误困扰了我很长时间。

I finally found this: Delimited Text-to-Columns in a Macro

我终于找到了这个： Delimited Text-to-Columns in a Macro

Which got me to this:

这让我想到了这个：

    Sub ConvertTextToNumber()
        Sheets("Worksheet_Name").Select
        Range("A1").Select
        Selection.TextToColumns _
            Destination:=Range("A:A"), _
            DataType:=xlDelimited
    End Sub

I use this in a macro to copy & reorder columns in a new sheet:

我在宏中使用它来复制和重新排序新工作表中的列：

    Sub ColumnReorder()
    '**********************************************************
    'Paste this macro into the Workbook of each new "Employee_List_Weekly_Update"
    'Functionality:
    '1. Column order in the "Employee_List_Weekly_Update" worksheet changes fairly often. 
    '   The macro will find each column by header name,
    '   select that column and copy it to the new sheet.
    '2. The macro also converts "Employee ID#" to a number,
    '   removing the "Number saved as Text" error.
    '**********************************************************
    'Create new sheet
        Sheets.Add.Name = "Roster_Columns_Reordered"

    'Repeat for each column or range
    'Find Column in "Employee_List_Weekly_Update" - Copy it - Paste it in "Roster_Columns_Reordered" - Employee ID#
        Dim a As Integer
        Sheets("Employee_List_Weekly_Update").Select
        Set rngData = Range("A1").CurrentRegion
        a = Application.WorksheetFunction.Match("Employee ID#", Range("A1:BB1"), 0)
        Columns(a).Select
        Selection.Copy

        Sheets("Roster_Columns_Reordered").Select
        Range("A1").Select
        ActiveSheet.Paste
    'Use TextToColumns to convert "Number Stored as Text "
        Selection.TextToColumns _
          Destination:=Range("A:A"), _
          DataType:=xlDelimited

    'Find Column in "Employee_List_Weekly_Update" - Copy it - Paste it in "Roster_Columns_Reordered" - Name
        Dim b As Integer
        Sheets("Employee_List_Weekly_Update").Select
        Set rngData = Range("A1").CurrentRegion
        b = Application.WorksheetFunction.Match("Name", Range("A1:BB1"), 0)
        Columns(b).Select
        Selection.Copy

        Sheets("Roster_Columns_Reordered").Select
        Range("B1").Select
        ActiveSheet.Paste

    'Go to "Roster_Columns_Reordered" - Add AutoFilter - Freeze Top Row
        Rows("1:1").Select
        Selection.AutoFilter
        With ActiveWindow
          .SplitColumn = 2
          .SplitRow = 1
        End With
        Rows("2:2").Select
        ActiveWindow.FreezePanes = True
        Range("A1").Select

    End Sub

回答by ProtoVB

Just use CDbl():

只需使用CDbl()：

ActiveSheet.Range("H154") = CDbl(ActiveSheet.Range("H154"))

回答by oyster

if you want to convert a selection (even with text in it!), you can use the code by firefiend (http://www.ozgrid.com/forum/showthread.php?t=64027&p=331498#post331498)

如果你想转换一个选择（即使里面有文本！），你可以使用 firefiend 的代码（http://www.ozgrid.com/forum/showthread.php?t=64027&p=331498#post331498）

I think the magic is in .Value = .Value

我认为魔法在 .Value = .Value

vba Sub macro() Range("F:F").Select 'specify the range which suits your purpose With Selection .NumberFormat = "General" .Value = .Value End With End Sub

vba Sub macro() Range("F:F").Select 'specify the range which suits your purpose With Selection .NumberFormat = "General" .Value = .Value End With End Sub