Convert an imported column from a text field to a number

Question

I am looking for a way to convert a text to a number, which can then be used further in other formulas. My columns look simple:

Data1   Data2
1       1
123     1324
1000    1000
...     ...

I tried several ways:

Range("S:S").NumberFormat = "0"
Range("T:T").NumberFormat = "0"

or copy an empty cell and add it to the range:

Sheets("Sold_Portfolio").Range("ZZ100000").Copy
Sheets("Sold_Portfolio").Range("T3:T100000").PasteSpecial , xlPasteSpecialOperationAd

However, besides seeing the right type there is only showing the count in the bottom of the Excel.

Any suggestions how to properly convert an Excel column to a number, which can then be used by other formulas further?

What is my error?

UPDATE Here you can find the full VBA code:

Sub selectFile()

Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet

Set wbI = ThisWorkbook
'sheet where you want to import the sheet in
Set wsI = wbI.Sheets("Sheet_Items")

ChDir (Environ("USERPROFILE") & "\Desktop")
 'Select the file
Fname = Application.GetOpenFilename(filefilter:="Text Files (*.txt),*.txt", MultiSelect:=False)

' check if file is selected
If Fname = False Then
'    MsgBox "No File Was Selected"
    Exit Sub
End If

' delete the current content there
Sheets("Sheet_Items").Range("G3:AB50000").ClearContents

'Set wbO = Workbooks.Open(Fname)
'
'wbO.Sheets(1).Cells.Copy wsI.Cells
'
'wbO.Close SaveChanges:=False
Dim var As String

var = "TEXT;" & Fname
With ActiveSheet.QueryTables.Add(Connection:= _
        var, Destination:=Range("$G$3") _
        )
        .Name = "Sample"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With



'You need to properly qualify the range for the Replace() method
Columns("S").Replace What:=".", _
                            Replacement:=",", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

Columns("T").Replace What:=".", _
                            Replacement:=",", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

'Range("S3:S100000") = Range("S3:S100000").Value
'Range("T3:T100000") = Range("T3:T100000").Value
'
'convert to number
'for further details on other available formats have a look at: http://stackoverflow.com/questions/20648149/what-are-numberformat-options-in-excel-vba
'Range("S:S").NumberFormat = "0"
'Range("T:T").NumberFormat = "0"

''get an emtpy cell
'Sheets("Sheet_Items").Range("ZZ100000").Copy
'Sheets("Sheet_Items").Range("S3:S100000").PasteSpecial , xlPasteSpecialOperationAdd
'
'Sheets("Sheet_Items").Range("ZZ100000").Copy
'Sheets("Sheet_Items").Range("T3:T100000").PasteSpecial , xlPasteSpecialOperationAdd

'select the cell B1
Sheets("Sheet_Items").Range("B1").Select

End Sub

The VALUE() function would work in a formula picking up the text in an adjacent column - and it's resilient if you have a mixture of numeric and non-numeric data. . Failing that, grab the column in a VBA array, loop through the array applying Clng() or Cdb() to each member, and write the array back into the range. — Nigel Heffernan, Nov 25 '15 at 14:22
Yeah, I'm a little confused. You say "Besides showing the right type..." So the cell's content `IsNumeric`? If so, then your cell is a number and it is the formula "showing the count in the bottom of the Excel" that is the problem. But @Nile is correct. `.Value` should treat any text that is numeric as a number. — Tim, Nov 25 '15 at 14:27
@pnuts Your method worked great. Can you explain me why? I would appreciate a detailed answer! — Carol.Kar, Nov 25 '15 at 15:26

score 2 · Accepted Answer · answered Nov 25 '15 at 15:39

You might run something like:

Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Tab:=True, FieldInfo:=Array(1, 1)

on each column (here A assumed).

Text to Columns is a Wizard that must be backed by proprietary code. One of its facilities is to permit the selection of the format for 'imported' data, which is then written into the existing column and parsed out to other columns (where (a) those are empty (unless that requirement is overridden) and (b) there is data to the right of the chosen delimiter or beyond the given fixed width).

The 'imported' data is what is actually already in the selected column. Since in this case that does not include any tabs, selecting Tab as the delimiter means that no parsing is involved - the data is read from the column and re-entered back into it. However as part of the re-entry process the format may be changed. In this case the second 1 parameter in the array defines numeric.

Proprietary code is usually much faster than VBA.

score 1 · Answer 2 · answered Nov 25 '15 at 14:30

1

Consider:

Sub qwerty()
   Dim r As Range, rng As Range
   Set rng = Intersect(Range("S:T"), ActiveSheet.UsedRange)
   rng.ClearFormats

   For Each r In rng
      r.Value = r.Value
   Next r
End Sub

answered Nov 25 '15 at 14:30

Gary's Student

95,722
10
59
99

Thx for your answer! However, my problem is that it can take ages to run through this code. – Carol.Kar Nov 25 '15 at 15:26
That's not a great answer: every 'hit' to the sheet, reading a cell, writing to a cell takes about a hundredth of a second. You know that you can read a range in a single 'hit' using `varArray = myRange.Value`, iterate through every item in the VBA array, and write it all back in a single 'hit' to the sheet as: `myRange.Value = varArray`? Iterating every array item from an entire column is slow, by VBA standards - so define your range properly - but that's still **way** faster than interacting with every cell directly, so it'll still run in under a second. – Nigel Heffernan Nov 26 '15 at 17:27

Convert an imported column from a text field to a number

2 Answers2