Formulas

Question

I export data to a CSV file each week and then paste into Excel. One of the columns contains information in a single cell which I than have to manually select the answer and insert to other cells. Is there a formula that could help with this?

{  "For which service or team are you applying to volunteer?": "Emergency Response",  "From where did you find out about this volunteer opportunity?": "Google search",  "If you're a student who became aware of this opportunity through your university, which university do you attend?": "",  "Have you had prior experience with the organisation, either as a staff member or a volunteer?": "No",  "Have you ever been a beneficiary or user of our services?": "No",  "Do you have lived experience of being an asylum seeker or refugee?": "No",  "Do you have personal experience of migration and/or family separation due to armed conflict or natural disaster?": "No"}

Answer 1

Not really, but as you have all data in your CSV, you can load it using Power Query and in there you can split that data, etc., finally load the data into the sheet as table.

The benefit is that you have to setup all this only once. Tomorrow, after you exported a new CSV file, simply click Data \ Refresh All and you're done.

If you need further help I need to see your (sample) file.
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Andreas.

Answer 2

Thank you Andreas. I have manged to split out the data using Power Query. I haven't yet figured out how to apply this when I paste fresh data in each week but I will play with it again tomorrow. Thank you for your help.