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
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
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)
Take C2 value, split the comma's, fill those values downwards (same for D and E)
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Are there ALWAYS the same number of comma separated items in C,D,E?
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Can you press button? Are macros enabled?
Macro you can find here (press Alt+F11 to show vba editor):
![macro]()
Macro you can find here (press Alt+F11 to show vba editor):
