Formula to transform this data into something that is readable for pivot tables

I have this file where the data comes from an online form. The problem is, the data is lumped into one single cell and I am trying to figure out if there is a single formula that I can use to efficiently transform this data into something that aligns with the kind of format that I want. I have here a sample worksheet where I inputted the current format of the data and the kind of format that I want it to have. 

Here is the link for the file: https://1drv.ms/x/s!AkZkG3PQHTAVhVhw7Jb2ZhLG4eZf?e=PaiQfJ