You essentially want to do a Split, to get it into separate columns, and then a Pivot, to get those entries into separate rows. However, Tableau doesn't appear to allow the pivoting of Split fields.
You could try to either:
1. set up a finite list of answers and then do a join to that list (see this post - https://community.tableau.com/message/675882#675882). Not sure if this will work with Excel though.
2. do the split in the Excel file. using these three steps:
a. edit Excel to:
- use Find command in Excel (see attached, columns M onwards)
- include a new RespID column as an index of the responses (to use in Tableau calculations)
b. pivot answer columns in Tableau, within Data Source preview area
c. Filter out false answers (see attached Word document for screenshots)
d. Set up new calculations that use RespID
- count distinct of RespID for 'Count of Responses'
- Level of Detail calculations for the 4 quantitative responses (Comfortable recommending, Remain customer etc.).
These act as "For each response, take the lowest response", because there can now be multiple rows for each respondent.
Make sure you do a similar LOD calculation for Age if you plan to average it.
Please mark answered if this works for you.