I need help separating responses from a survey into different columns. Each "check all that apply" question has the responses from each respondent in one cell (e.g. 1,3,4 or 1,2 or 2,4, etc.). For example, I want to create x number of columns for all the answer choices, then code the responses 'yes' or 'no' in excel.

Q2
1,2,3
2,3,4
3,4
1,3,4
1,2,4
...

I learned how to separate the column by comma using Text to column but this is the code after I separate it:

Q2
1  2  3
2  3  4
3  4
1  3  4
1  2  4
...

What I want is each column have a similar value per row. Here is an example :

Q2
1  2  3
   2  3  4
      3  4
1     3  4
1  2     4
...

Is there a way to do it without moving each cell manually since there is like 100 answer? Thanks

CodePudding user response：

For Office 365 Insider Channel:

=LET(ζ,0 TEXTSPLIT(A1,","),XLOOKUP(SEQUENCE(,MAX(ζ)),ζ,ζ,""))

Copy down to get similar results for the strings in A2, A3, etc.

CodePudding user response：

If you didn't have access to Office 365 insider, you could do a similar thing using Split in VBA:

Sub test()

    Dim LString As String
    Dim LArray() As String
    ' Change to Long for larger ranges (question only required 100 rows)
    Dim i As Integer, j As Integer, k As Integer, lastElement As Integer, LR As Integer
    
    Const LC = 5
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To LR
    
        LString = Cells(i, 1)
        LArray = Split(LString, ",")
        
        lastElement = UBound(LArray, 1)
        
        k = 0
    
        For j = 1 To LC
            
            If k > lastElement Then
                Exit For
            Else
                If LArray(k) = j Then
                    Cells(i, j   1) = LArray(k)
                    k = k   1
                End If
            End If
        Next j
    Next i

End Sub

Assumes responses in each row are in ascending order and output range is initially blank.