VBA Macro to split cells at every ";"

[drew]

I am currently using this code to find every cell in column b1 and to find the ones that contain a ";" something like "hello;goodbye". The code will split the cell at the ";" and place "goodbye" directly beneath "hello;" on an entirely new row..

What I need however is this... if a cell contains multiple ";" ("hello;goodbye;yo;hi;hey") it will split at EACH ";" not just the first and then move each to a new row directly beneath the other...

What changes do I need to make?


Dim r1 As Range, r2 As Range
Dim saItem() As String


For Each r1 In ActiveSheet.Range("B1", Cells(Application.Rows.Count, 2).End(xlUp))
If InStr(1, r1.Value2, ";") > 0 Then
saItem = Split(r1.Value2, ";")
r1 = Trim$(saItem(0)) & ";"
r1.Offset(1).EntireRow.Insert (xlDown)
r1.Offset(1) = Trim$(saItem(1))
End If
Next r1

[drew]

Found an answer over at excelforum.com here is the solution I was given:


Sub tgr() Dim rindex As Long Dim saItem() As String For rindex = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1 If InStr(Cells(rindex, "B").Value, ";") > 0 Then saItem = Split(Cells(rindex, "B").Value, ";") Rows(rindex + 1 & ":" & rindex + UBound(saItem)).Insert Cells(rindex, "B").Resize(UBound(saItem) + 1).Value = WorksheetFunction.Transpose(saItem) End If Next rindex End Sub

[santhoshkv]

Hi drew,
Hope this helps,

Dim sv_Row, sv_Rowi
sv_Row = Cells(Rows.Count, 2).End(xlUp).Row

For sv_Rowi = sv_Row To 1 Step -1
sv_temp = Worksheets("Sheet2").Cells(sv_Rowi, 2).Value
If InStr(1, sv_temp, ";") > 0 Then
sv_tempval = Split(sv_temp, ";")
Worksheets("Sheet2").Cells(sv_Rowi, 2).Value = sv_tempval(0)
For i = 1 To UBound(sv_tempval)
Rows(sv_Rowi + i & ":" & sv_Rowi + i).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Worksheets("Sheet2").Cells(sv_Rowi + i, 2).Value = sv_tempval(i)
Next
End If
Next

[JP2112]

Please don't multipost:


http://stackoverflow.com/questions/8231368/split-strings-in-excel-vba
http://www.excelforum.com/excel-programming/802602-vba-macro-to-split-cells-at-every.htmlAs you were told on Excel Forum, please use [ vba ] tags when posting code.