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

Question

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?

have you tried to just change the format of the range .NumberFormat = "@" ? — Nathan_Sav
– Nathan_Sav, Commented Jul 3, 2017 at 14:58
@Nathan_Sav It did not work. A1 = 15 and A1="15" is still givning me false — Jordan
– Jordan, Commented Jul 4, 2017 at 9:12

XsiSecOfficial · Accepted Answer · 2017-07-04 16:02:22Z

1

is this what you looking for?

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

Cheers

Elmnas

answered Jul 4, 2017 at 16:02

XsiSecOfficial

9668 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jordan Over a year ago

Not really because If B2=15 and I use .NumberFormat = "@" then B2="15" is still false. @DanielElmnas

XsiSecOfficial · Accepted Answer · 2017-07-10 09:16:38Z

1

Try this then:

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

answered Jul 10, 2017 at 9:16

XsiSecOfficial

9668 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related