All the data is inside a single cell, need help cleaning

Question

I have data coming from a system and it is outputting all the data inside a single cell. The data is not consistent, such that it is not coming as the output in the same format. I have attached a example below.

My goal is to separate the data and then convert it into columns. I have also attached the desired output in the below excel sheet. The number of fields that may show up vary from between the cells.

The steps I tried was to create a Batch macro, which takes in one cell at a time, delimits it based on , which is ignored in quotes. This helped to separate the fields out in to columns but I could not figure out how to go from 'upc': '015532100821' to a column names upc and value of it as 015532100821.

Any help on this would be really appreciated.

Thank you,

Book1.xlsx

joshuaburkhow · Accepted Answer

This isn't a complete solution but I wanted to get you a start so you can see how you can get there. You'll need to do a bit of work to get just perfect. The big piece is to add the formula tool first to clean it up a bit so it's easier to parse...

Hope this helps!