Split text to multiple columns using VBA code

[Kenor]

Hi All,

I would like to create VBA code for split a long text (items) into multiple columns as below picture (How to split the items) by using column button (Split Text)

I used the code below but it didn't work while I was using it.

Sub splitText()
'splits Text active cell using ALT+10 char as separator
Dim splitVals As Variant
Dim totalVals As Long

splitVals = Split(ActiveCell.Value, Chr(10))
totalVals = UBound(splitVals)

Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
End Sub


My target is as per picture of Result after split.

Anyone can help me.

Thanks

 

[Anthony47]

Your Separator is chr(32), and you should insert the splitting code in a loop. May be this:

Sub RSplit()
    For I = 2 To Cells(Rows.Count, 3).End(xlUp).Row
        mysplit = Split(Cells(I, 3).Value & " #", " ", , vbTextCompare)
        Cells(I, 3).Resize(1, UBound(mysplit)).Value = mysplit
    Next I
End Sub

Try...

 

[Kenor]

Your Separator is chr(32), and you should insert the splitting code in a loop. May be this:

Sub RSplit()
    For I = 2 To Cells(Rows.Count, 3).End(xlUp).Row
        mysplit = Split(Cells(I, 3).Value & " #", " ", , vbTextCompare)
        Cells(I, 3).Resize(1, UBound(mysplit)).Value = mysplit
    Next I
End Sub

Try...

Thanks a lot...its works!