Converting Data into Text Using Text to Column with VBA

[razgra1z]

Hello All,

Following VBA code converts data in column H into text. But it only works on column H, how can we update this code so that it can work on active column or active cells range. Thank you.

Columns("H:H").Select
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True

 

[Joe4]

Text to columns can only run on one column at a time.

So the following will run on whatever column the active cell is found in.

Dim c as Long
c = ActiveCell.Column
Columns(c).TextToColumns Destination:=Cells(1, c), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 2), TrailingMinusNumbers:=True

 

[razgra1z]

Hello Joe4

Many thanks for the update. I am having one issue with this code now; when i apply this code although it converts the data into text but it offsets the data one cell up. How can we resolve it??

 

[Alex Blakenburg]

Hello Joe4

Many thanks for the update. I am having one issue with this code now; when i apply this code although it converts the data into text but it offsets the data one cell up. How can we resolve it??

Just make the destination the same as the initial selection.

Dim c As Long
c = ActiveCell.Column
Columns(c).TextToColumns Destination:=Columns(c), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 2), TrailingMinusNumbers:=True

 

[razgra1z]

Nope, still not working

 

[Alex Blakenburg]

Can supply a picture of before and after.
And the actual code you used.

 

[Joe4]

And what is your active cell when you go to run your code?

 

[razgra1z]

I have attached the pics before and after macro. Currently, I am applying this code on column H but I have multiple reports where I have to change this 10 digits figure into text (column can be changed).

 

[Alex Blakenburg]

1) Can you show us the code that are using ?
Preferably the whole sub procedure.

2) If you want to try yourself first, do you know how to set a breakpoint ?

• Click in the margin of the 1st line in text to columns you will get brown dots in the left margin and highlight the lines in brown
• When you run the code it will stop there.
• Check the sheet to see if the row has not already been offset before running the TextToColumns line
• go back to the code and hit <F8> to run the text to columns line and then go back to the sheet to see if at that point it has offset by 1 row.
  (we need to confirm it is actually that exact line causing the problem since I can't replicate the problem)
• If it is still fine at that point we need to examine the rest of the code.
  (You can keep hitting F8 is you want to step through the code line by line or hit the Stop button to finish.

@Joe4 - I have tried selecting various cells ie single cell, multiple cells, whole column. They all work fine and on the whole column. They also leave the initial selection highlighted, so from razgra1z's "post running" macro screenshot I would say the whole column was selected.

Sorry I am Australia, so timezone wise so my responses are more often than not overnight.

 

[razgra1z]

I am using the following code:

Sub Macro1_Test()

Dim c As Long
c = ActiveCell.Column
Columns(c).TextToColumns Destination:=Columns(c), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True

End Sub