CODEWITHSUNDEEP
vbaexcelformattingexcel-2010
Carol.Kar
Carol.Kar

Reputation: 5345

Convert an imported column from a text field to a number

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

Upvotes: 0

Views: 230

Answers (2)

pnuts
pnuts

Reputation: 59432

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.

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 1

Related Questions