CODEWITHSUNDEEP
excelvba
user1283776
user1283776

Reputation: 21814

Convert numbers stored as text to numbers?

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.

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

Upvotes: 1

Views: 16349

Answers (4)

oyster
oyster

Reputation: 567

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)

I think the magic is in .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

Upvotes: 2

Kris Walsh
Kris Walsh

Reputation: 129

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

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

Upvotes: 3

ProtoVB
ProtoVB

Reputation: 793

Just use CDbl():

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

Upvotes: 3

chris neilsen
chris neilsen

Reputation: 53136

A general technique is to Copy PasteSpecial, Multiply by 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

Upvotes: 6

Related Questions