Text to columns to a different sheet using VBA.!?

Bumba

New Member
Joined
Jan 29, 2019
Messages
10
Hi people,

How do I split text from a range in a column (maybe using text to column, separator "x") to a different worksheet and rearrange. To make you understand better here is a image of the source data(Sheet1)
Untitled.png


My expected output is something like(Sheet2):
Untitled2.png

I've done
VBA Code:
Sub customSplit()
    Dim objRange1 As Range
    
    Set objRange1 = Range("A2:A6")
    
    objRange1.TextToColumns _
      Destination:=ActiveWorkbook.Worksheets("Sheet2").Range("F2"), _
      DataType:=xlDelimited, _
      Tab:=False, _
      Semicolon:=False, _
      Comma:=False, _
      Space:=False, _
      Other:=True, _
      OtherChar:="x"

End Sub
But it pastes the result in Sheet1 & also I cannot find any option to rearrange the data to my needs.
Please help me!!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Bumba()
    Dim Cl As Range
    Dim Sp As Variant
    
    With Sheets("Sheet1")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            Sp = Split(Cl.Value, "x")
            Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sp(2), Sp(0), Sp(1))
        Next Cl
    End With
End Sub
 

Bumba

New Member
Joined
Jan 29, 2019
Messages
10
Thank you.:)
BTW, can you explain the below line in your code:
Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sp(2), Sp(0), Sp(1))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
That line find the last used row in col F, offsets down 1 line, then resizes by 3 columns.
So if F1 has a value it would give a range of F2:H2 then it puts the values in the Sp array into those cells with the last value of the array going into H the first value into G & the middle value into H
HTH
 

Bumba

New Member
Joined
Jan 29, 2019
Messages
10

ADVERTISEMENT

Thanks again for explaining...One last thing out of curiosity though, if I wanted the values in Sheet2 columns say F,H & J instead of F,G & H how can I do that?

Also, How do I mark this post as solved or answered :)?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
if I wanted the values in Sheet2 columns say F,H & J instead of F,G & H how can I do that
You would have to do it individually for each cell.

We don't mark threads as solved here. :)
 

Bumba

New Member
Joined
Jan 29, 2019
Messages
10

ADVERTISEMENT

Well, if I do it like below it messes things up:
VBA Code:
            Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sp(2))
            Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sp(0))
            Sheets("Sheet2").Range("J" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sp(1))

What am I doing wrong?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
It should either be like
VBA Code:
Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1) = Sp(2)
or slightly simpler
VBA Code:
            With Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1)
                .Value = Sp(2)
                .Offset(, 2).Value = Sp(0)
               .Offset(, 4).Value = Sp(1)
            End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,189
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,136,854
Messages
5,678,138
Members
419,746
Latest member
tysonboy82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top