Split string in adjacent cells

[Marco1975]

Hello everyone ,
I need to make a macro that split the strings contained in the column b in adjacent cells . The parts for export are separated by commas.
for example in the column b2 we find :

ciao, come stai, spero bene, ci sentiamo presto

the end result should be :
c2 : ciao
d2 : come stai
e2 : spero bene
f2 : ci sentiamo presto

The operation should be done by b2 to the end of the column b .

Attach files with excel example. Thanks a lot.

 

[Deepak]

Hi,

In a formula way it could be achieved by uniform formula from C2 to F2

=TRIM(MID(SUBSTITUTE($B$2,",",REPT(" ",99)),99*(COLUMN()-3)+1,99))

Same by VBA

Sub split_cells()

Range("C2:F" & Range("B2").CurrentRegion.Rows.Count) = _
"=TRIM(MID(SUBSTITUTE($B$2,"","",REPT("" "",99)),99*(COLUMN()-3)+1,99))"

'to convet into values
'Range("C2:F" & Range("B2").CurrentRegion.Rows.Count).Value = _
Range("C2:F" & Range("B2").CurrentRegion.Rows.Count).Value

End Sub

 

[Deepak]

And Another one!!

Sub Split1()
Dim SplitArray() As String, r As Integer, i As Integer

For r = 2 To Range("B2").CurrentRegion.Rows.Count + 1

SplitArray = Split(Cells(r, 2), ", ", , vbTextCompare)
   
    For i = LBound(SplitArray) To UBound(SplitArray)
        Cells(r, i + 3) = SplitArray(i)
    Next

Next

End Sub

 

[Marco1975]

Deepak thanks for answering the ultimate solution is the best!!!!

 

[Hui]

If you have a column of similar strings you can use the Data, Text to Column menu
Select , as a delimiter

 

[Marc L]

Hi,

and in VBA, just needs a single codeline using TextToColumns method !
​

 

[Hui]

Taking Marc L's concept

The following code will do what you want

Sub Split_String_at_Commas()
Dim c As Range
For Each c In Selection
  c.TextToColumns _
  Destination:=c.Offset(0, 1), _
  DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=False, _
  Tab:=True, _
  Semicolon:=False, _
  Comma:=True, _
  Space:=False, _
  Other:=False, _
  FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
  TrailingMinusNumbers:=True
Next
End Sub

Select that and copy to VBA

Select a cell or range of cells
Alt+F8
Select Split_String_at_Commas
Press Run

Enjoy

 

[Marco1975]

Thank you Hui... great solution!!!

 

[Marc L]

TextToColumns works directly with a column range (no loop) :​

Sub Demo()
    Range("B2", Cells(Rows.Count, 2).End(xlUp)).TextToColumns [C2], xlDelimited, xlTextQualifierNone, , , , True
End Sub