• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split string in adjacent cells

Marco1975

New Member
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.
 

Attachments

  • STRING.xlsx
    11.3 KB · Views: 0
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

Code:
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
 
And Another one!!

Code:
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
 
If you have a column of similar strings you can use the Data, Text to Column menu
Select , as a delimiter
 
Taking Marc L's concept

The following code will do what you want

Code:
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
 

TextToColumns works directly with a column range (no loop) :​
Code:
Sub Demo()
    Range("B2", Cells(Rows.Count, 2).End(xlUp)).TextToColumns [C2], xlDelimited, xlTextQualifierNone, , , , True
End Sub
 
Back
Top