Fields and values bundled into one cell and text string

pattaman

New Member
Joined
Sep 23, 2019
Messages
5
Office Version
365
Platform
MacOS
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!
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,192
Office Version
365
Platform
Windows
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
XYZAAABAC
1
2{'Field_A': 'A1', 'Field_B': 'B1', 'Field_C': 'C1', 'Field_D': 'D1', 'Field_E': 'E1'}A1B1C1D1E1
3Field_A: A1Field_B: B1Field_C: C1Field_D: D1Field_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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,343
Messages
5,486,289
Members
407,538
Latest member
kbendelac

This Week's Hot Topics

Top