Parsing data into columns

[kingwombat]

I was hoping someone would be able to assist me with a question regarding parsing data. I have some information that I want to parse to columns. However, the data is inconsistent. Normally, if the data was uniform and consistent, I could have easily used text to columns.

Here's a sample of the data:

properties

{'document_type': 'driving_licence', 'date_of_expiry': '2021-10-26', 'issuing_country': 'ITA'}

{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2022-01-17', 'issuing_country': 'GBR'}

{'gender': 'Male', 'nationality': 'ITA', 'document_type': 'passport', 'date_of_expiry': '2019-12-03', 'issuing_country': 'ITA'}

{'document_type': 'driving_licence', 'date_of_expiry': '2025-09-18', 'issuing_country': 'IRL'}

{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2025-03-05', 'issuing_country': 'GBR'}

{'nationality': 'DEU', 'document_type': 'national_identity_card', 'date_of_expiry': '2026-01-03', 'issuing_country': 'DEU'}

{'gender': 'Female', 'nationality': 'NLD', 'document_type': 'national_identity_card', 'date_of_expiry': '2017-06-12', 'issuing_country': 'NLD'}

{'gender': 'Female', 'document_type': 'national_identity_card', 'date_of_expiry': '2020-08-20', 'issuing_country': 'ESP'}

{'document_type': 'driving_licence', 'date_of_expiry': '2022-01-24', 'issuing_country': 'LTU'}

{'gender': 'Male', 'nationality': 'PHL', 'document_type': 'passport', 'date_of_expiry': '2022-01-24', 'issuing_country': 'PHL'}

{'gender': 'Male', 'nationality': 'GBR', 'document_type': 'passport', 'date_of_expiry': '2018-11-10', 'issuing_country': 'GBR'}

{'gender': 'Male', 'document_type': 'driving_licence', 'issuing_state': 'CA', 'date_of_expiry': '2020-03-23', 'issuing_country': 'USA'}

{'gender': 'Male', 'nationality': 'IRL', 'document_type': 'passport', 'date_of_expiry': '2024-11-03', 'issuing_country': 'IRL'}

{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2019-06-12', 'issuing_country': 'GBR'}

{'gender': 'Female', 'nationality': 'ITA', 'document_type': 'passport', 'date_of_expiry': '2023-10-03', 'issuing_country': 'ITA'}

{'gender': 'Male', 'nationality': 'ESP', 'document_type': 'national_identity_card', 'date_of_expiry': '2024-06-23', 'issuing_country': 'ESP'}

{'document_type': 'driving_licence', 'date_of_expiry': '2017-08-23', 'issuing_country': 'SWE'}

{'gender': 'Male', 'nationality': 'FRA', 'document_type': 'passport', 'date_of_expiry': '2024-08-05', 'issuing_country': 'FRA'}

{'gender': 'Female', 'nationality': 'FRA', 'document_type': 'passport', 'date_of_expiry': '2023-11-13', 'issuing_country': 'FRA'}

{'gender': 'Female', 'nationality': 'ZAF', 'document_type': 'passport', 'date_of_expiry': '2026-03-15', 'issuing_country': 'ZAF'}

{'gender': 'Male', 'nationality': 'ESP', 'document_type': 'national_identity_card', 'date_of_expiry': '2019-04-28', 'issuing_country': 'ESP'}

{'gender': 'Female', 'document_type': 'national_identity_card', 'issuing_country': 'ZAF'}

{'gender': 'Male', 'nationality': 'AUT', 'document_type': 'national_identity_card', 'date_of_expiry': '2027-03-26', 'issuing_country': 'AUT'}

{'gender': 'Male', 'nationality': 'BEL', 'document_type': 'national_identity_card', 'date_of_expiry': '2025-04-03', 'issuing_country': 'BEL'}

{'gender': 'Male', 'nationality': 'BEL', 'document_type': 'national_identity_card', 'date_of_expiry': '2025-04-03', 'issuing_country': 'BEL'}

{'gender': 'Female', 'nationality': 'GBR', 'document_type': 'passport', 'date_of_expiry': '2024-06-26', 'issuing_country': 'GBR'}

{'gender': 'Female', 'nationality': 'MEX', 'document_type': 'passport', 'date_of_expiry': '2018-11-11', 'issuing_country': 'MEX'}

{'gender': 'Female', 'nationality': 'MAR', 'document_type': 'passport', 'date_of_expiry': '2020-12-22', 'issuing_country': 'MAR'}

{'gender': 'Male', 'nationality': 'PRT', 'document_type': 'national_identity_card', 'date_of_expiry': '2019-11-23', 'issuing_country': 'PRT'}

{'gender': 'Male', 'document_type': 'national_identity_card', 'issuing_country': 'SGP'}

{'gender': 'Male', 'nationality': 'TUR', 'document_type': 'passport', 'date_of_expiry': '2023-09-29', 'issuing_country': 'TUR'}

I want to parse the data into the following columns:

gender

nationality

document_type

date_of_expiry

issuing_country

issuing_date

issuing_state

document_version

If someone could help me out, it would mean a lot.

 

[Anthony47]

Is this information already on a worksheet or where?

 

[kingwombat]

yes, It's in a worksheet

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	properties	gender	nationality	document_type	date_of_expiry	issuing_country	issuing_date	issuing_state	document_version
2	{'document_type': 'driving_licence', 'date_of_expiry': '2021-10-26', 'issuing_country': 'ITA'}			driving_licence	2021-10-26	ITA
3	{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2022-01-17', 'issuing_country': 'GBR'}	Female		driving_licence	2022-01-17	GBR
4	{'gender': 'Male', 'nationality': 'ITA', 'document_type': 'passport', 'date_of_expiry': '2019-12-03', 'issuing_country': 'ITA'}	Male	ITA	passport	2019-12-03	ITA
5	{'document_type': 'driving_licence', 'date_of_expiry': '2025-09-18', 'issuing_country': 'IRL'}			driving_licence	2025-09-18	IRL
6	{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2025-03-05', 'issuing_country': 'GBR'}	Female		driving_licence	2025-03-05	GBR
7	{'nationality': 'DEU', 'document_type': 'national_identity_card', 'date_of_expiry': '2026-01-03', 'issuing_country': 'DEU'}		DEU	national_identity_card	2026-01-03	DEU
8	{'gender': 'Female', 'nationality': 'NLD', 'document_type': 'national_identity_card', 'date_of_expiry': '2017-06-12', 'issuing_country': 'NLD'}	Female	NLD	national_identity_card	2017-06-12	NLD
9	{'gender': 'Female', 'document_type': 'national_identity_card', 'date_of_expiry': '2020-08-20', 'issuing_country': 'ESP'}	Female		national_identity_card	2020-08-20	ESP
10	{'document_type': 'driving_licence', 'date_of_expiry': '2022-01-24', 'issuing_country': 'LTU'}			driving_licence	2022-01-24	LTU
11	{'gender': 'Male', 'nationality': 'PHL', 'document_type': 'passport', 'date_of_expiry': '2022-01-24', 'issuing_country': 'PHL'}	Male	PHL	passport	2022-01-24	PHL
12	{'gender': 'Male', 'nationality': 'GBR', 'document_type': 'passport', 'date_of_expiry': '2018-11-10', 'issuing_country': 'GBR'}	Male	GBR	passport	2018-11-10	GBR
13	{'gender': 'Male', 'document_type': 'driving_licence', 'issuing_state': 'CA', 'date_of_expiry': '2020-03-23', 'issuing_country': 'USA'}	Male		driving_licence	2020-03-23	USA		CA
14	{'gender': 'Male', 'nationality': 'IRL', 'document_type': 'passport', 'date_of_expiry': '2024-11-03', 'issuing_country': 'IRL'}	Male	IRL	passport	2024-11-03	IRL
15	{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2019-06-12', 'issuing_country': 'GBR'}	Female		driving_licence	2019-06-12	GBR
16	{'gender': 'Female', 'nationality': 'ITA', 'document_type': 'passport', 'date_of_expiry': '2023-10-03', 'issuing_country': 'ITA'}	Female	ITA	passport	2023-10-03	ITA
17	{'gender': 'Male', 'nationality': 'ESP', 'document_type': 'national_identity_card', 'date_of_expiry': '2024-06-23', 'issuing_country': 'ESP'}	Male	ESP	national_identity_card	2024-06-23	ESP
18	{'document_type': 'driving_licence', 'date_of_expiry': '2017-08-23', 'issuing_country': 'SWE'}			driving_licence	2017-08-23	SWE
19	{'gender': 'Male', 'nationality': 'FRA', 'document_type': 'passport', 'date_of_expiry': '2024-08-05', 'issuing_country': 'FRA'}	Male	FRA	passport	2024-08-05	FRA
20	{'gender': 'Female', 'nationality': 'FRA', 'document_type': 'passport', 'date_of_expiry': '2023-11-13', 'issuing_country': 'FRA'}	Female	FRA	passport	2023-11-13	FRA
21	{'gender': 'Female', 'nationality': 'ZAF', 'document_type': 'passport', 'date_of_expiry': '2026-03-15', 'issuing_country': 'ZAF'}	Female	ZAF	passport	2026-03-15	ZAF
22	{'gender': 'Male', 'nationality': 'ESP', 'document_type': 'national_identity_card', 'date_of_expiry': '2019-04-28', 'issuing_country': 'ESP'}	Male	ESP	national_identity_card	2019-04-28	ESP
23	{'gender': 'Female', 'document_type': 'national_identity_card', 'issuing_country': 'ZAF'}	Female		national_identity_card		ZAF
24	{'gender': 'Male', 'nationality': 'AUT', 'document_type': 'national_identity_card', 'date_of_expiry': '2027-03-26', 'issuing_country': 'AUT'}	Male	AUT	national_identity_card	2027-03-26	AUT
25	{'gender': 'Male', 'nationality': 'BEL', 'document_type': 'national_identity_card', 'date_of_expiry': '2025-04-03', 'issuing_country': 'BEL'}	Male	BEL	national_identity_card	2025-04-03	BEL
26	{'gender': 'Male', 'nationality': 'BEL', 'document_type': 'national_identity_card', 'date_of_expiry': '2025-04-03', 'issuing_country': 'BEL'}	Male	BEL	national_identity_card	2025-04-03	BEL
27	{'gender': 'Female', 'nationality': 'GBR', 'document_type': 'passport', 'date_of_expiry': '2024-06-26', 'issuing_country': 'GBR'}	Female	GBR	passport	2024-06-26	GBR
28	{'gender': 'Female', 'nationality': 'MEX', 'document_type': 'passport', 'date_of_expiry': '2018-11-11', 'issuing_country': 'MEX'}	Female	MEX	passport	2018-11-11	MEX
29	{'gender': 'Female', 'nationality': 'MAR', 'document_type': 'passport', 'date_of_expiry': '2020-12-22', 'issuing_country': 'MAR'}	Female	MAR	passport	2020-12-22	MAR
30	{'gender': 'Male', 'nationality': 'PRT', 'document_type': 'national_identity_card', 'date_of_expiry': '2019-11-23', 'issuing_country': 'PRT'}	Male	PRT	national_identity_card	2019-11-23	PRT
31	{'gender': 'Male', 'document_type': 'national_identity_card', 'issuing_country': 'SGP'}	Male		national_identity_card		SGP
32	{'gender': 'Male', 'nationality': 'TUR', 'document_type': 'passport', 'date_of_expiry': '2023-09-29', 'issuing_country': 'TUR'}	Male	TUR	passport	2023-09-29	TUR
List

Cell Formulas
Range	Formula
B2:I32	B2	=IFERROR(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID($A2,FIND(B$1,$A2)+LEN(B$1)+3,200),",",REPT(" ",100)),"}",""),100)),"'",""),"")

 

[kingwombat]

Thank you very much! this works

 

[Fluff]

You're welcome & thanks for the feedback