Jordan
Jordan

Reputation: 697

How to convert a column filled with integer to text format - VBA - Excel

I'm trying to convert all data in column B , which currently are integers ex. 26, to text format, ex "26". I have tried the following:

Dim index As Long

index = 2

Do While index_5 <> NewResizeRange

   Worksheets("Source").Cells(index, "B").Value = CStr(Worksheets("Source").Cells(index, "B").Value)

   index = index + 1

Loop

Which turned to be extremely slow and ineffective.

I also tried:

Sub Macro8()

   Columns("E:E").Select
   Selection.TextToColumns Destination:=Range("Table1[[#Headers],[Column5]]"), _
    DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
    :=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
    Other:=False, FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
End Sub

Problem is that when the data comes from another worksheet and the cell is refering to said worksheet it is only displaying the location of the data in the cell.

Any ideas?

Upvotes: 0

Views: 137

Answers (2)

XsiSecOfficial
XsiSecOfficial

Reputation: 964

Try this then:

Range("D1:D999999").FormulaR1C1 = "=TEXT(RC[-3],""0"")"
Range("D1:D999999").Copy 
Range("A1:A999999").PasteSpecial Paste:=xlPasteValues

Upvotes: 1

XsiSecOfficial
XsiSecOfficial

Reputation: 964

is this what you looking for?

Range("B1:B288889").NumberFormat = "@"

Cheers

Elmnas

Upvotes: 1

Related Questions