What is your data structure?
I believe Survey Data is most easily manipulated when it is vertical. And there's a row for every response.
Unfortunately I'm not sure of a good way to handle this. I would think to clean the data up first and have it structured such as:
Responder ID | Question | Answer
I would say, after pivoting your data, you could union your data as many times as possible replies. And each for union (eg. Table 2), you filter to only take the second response per question.
Unfortunately with SPLIT it has to be a string literal, so I would maybe replace all your line breaks with a pipe or the like.
But then you could do something like SPLIT([Response], '|', 2) for table 2. And only return when not null. You'd have to repeat this for each possible # of answers.
It's a bit of work, but possible.
Zen Master and HOF Steve Wexler is the go to when it comes to structuring survey data
this would be a starting point Steve Wexler | Tableau Software
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.