Fields and values bundled into one cell and text string

[pattaman]

Hi Guys,

I'm cleaning a fairly sizable data set, approx 200,000 rows and I'm stuck on the final bit I need to clean so I can analyse the data effectively.

There is one column where between 3 and 5 fields are bundled into one text string and I am struggling to separate the fields and values into corresponding columns in order to be able to pivot etc.

Not all cells contain the same fields, and there are multiple values for each field.

The cell composition is as below:

Cell: X2
{'Field_A': 'A1', 'Field_B': 'B1', 'Field_C': 'C1', 'Field_D': 'D1', 'Field_E': 'E1'}

Cell: X16
{'Field_C': 'C2', 'Field_D': 'D2', 'Field_E': 'E2'}

Cell: X18
{'Field_A': 'A1', 'Field_B': 'B2', 'Field_C': 'C1', 'Field_D': 'D3', 'Field_E': 'E3'}

These are examples of the difference in the text strings within cells going down the X column.

Any geniuses that are out there able to clean this formatting for me your help will be hugely appreciated!

 

[Peter_SSs]

Are you saying that this is exactly what might appear in the cells in column X? If not can you clarify with some exact examples.

What exactly do want as the result? Maybe the yellow, green or something else for that first example?

Excel Workbook
	X	Y	Z	AA	AB	AC
1
2	{'Field_A': 'A1', 'Field_B': 'B1', 'Field_C': 'C1', 'Field_D': 'D1', 'Field_E': 'E1'}	A1	B1	C1	D1	E1
3		Field_A: A1	Field_B: B1	Field_C: C1	Field_D: D1	Field_E: E1
4
5
15
16	{'Field_C': 'C2', 'Field_D': 'D2', 'Field_E': 'E2'}
17
18	{'Field_A': 'A1', 'Field_B': 'B2', 'Field_C': 'C1', 'Field_D': 'D3', 'Field_E': 'E3'}
Sheet1