We help IT Professionals succeed at work.

comma separated array into  rows

70 Views
Last Modified: 2019-01-14
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
Comment
Watch Question

KimputerIT Manager
CERTIFIED EXPERT

Commented:
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)
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Are there ALWAYS the same number of comma separated items in C,D,E?

Author

Commented:
no
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
does not show up in macros
CERTIFIED EXPERT

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

Author

Commented:
there is no macro
CERTIFIED EXPERT

Commented:
Can you press button? Are macros enabled?
Macro you can find here (press Alt+F11 to show vba editor):
macro
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
No
Then what is/are the rule(s) for parsing this into multiple rows?

Author

Commented:
sorry must have been wrong setting ony my other computer.

Author

Commented:
thx!!