Link to home
Start Free TrialLog in
Avatar of finnstone
finnstone

asked on

comma separated array into rows

long list of rows like the one in the attached in row 1.

i need to split it into two rows as i did in the below cells. sometimes three rows if there are 3 entries in column C separated by col.

as you can see 1st entries in C,D,E go into their own new row. 2nd entries go into a new other row
Book27.xlsx
Avatar of Kimputer
Kimputer

Probably VBA would be the easiest way to go.
Take C2 value, split the comma's, fill those values downwards (same for D and E)
Are there ALWAYS the same number of comma separated items in C,D,E?
Avatar of finnstone

ASKER

no
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
does not show up in macros
It is in worksheet's code
Private Sub split_text_Click()
Dim words_found() As String, prematch() As String, aftermatch() As String
Dim R As Long, R1 As Integer
Dim Res As Worksheet, Src As Worksheet
Dim Str As Range
Dim i As Long, j As Integer
With ActiveWorkbook
    Set Src = .Worksheets("Sheet1") 'Correct name if it is different
    'Add new worksheet for results
    Set Res = .Worksheets.Add
End With
'Fill Headeres
With Src
    .Range("A1:E1").Copy Res.Range("A1:E1")
    Res.Range("A:E").Columns.AutoFit
    R = 2
    R1 = 2
   Do While .Cells(R, 1) <> ""
    words_found = Split(.Cells(R, 3), ",")
    prematch = Split(.Cells(R, 4), ",")
    aftermatch = Split(.Cells(R, 5), ",")
    i = WorksheetFunction.Max(UBound(words_found), UBound(prematch), UBound(aftermatch))
    For j = 0 To i
        .Range("A" & R & ":B" & R).Copy Res.Range("A" & R1)
        If UBound(words_found) >= j Then Res.Cells(R1, 3) = words_found(j)
        If UBound(prematch) >= j Then Res.Cells(R1, 4) = prematch(j)
        If UBound(aftermatch) >= j Then Res.Cells(R1, 5) = aftermatch(j)
        R1 = R1 + 1
    Next j
    R = R + 1
   Loop
End With
End Sub

Open in new window

there is no macro
Can you press button? Are macros enabled?
Macro you can find here (press Alt+F11 to show vba editor):
User generated image
No
Then what is/are the rule(s) for parsing this into multiple rows?
sorry must have been wrong setting ony my other computer.
thx!!