Parsing data into columns

kingwombat

New Member
Joined
Apr 6, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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:

gendernationalitydocument_typedate_of_expiryissuing_countryissuing_dateissuing_statedocument_version

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

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,816
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about

+Fluff.xlsm
ABCDEFGHI
1propertiesgendernationalitydocument_typedate_of_expiryissuing_countryissuing_dateissuing_statedocument_version
2{'document_type': 'driving_licence', 'date_of_expiry': '2021-10-26', 'issuing_country': 'ITA'}  driving_licence2021-10-26ITA   
3{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2022-01-17', 'issuing_country': 'GBR'}Female driving_licence2022-01-17GBR   
4{'gender': 'Male', 'nationality': 'ITA', 'document_type': 'passport', 'date_of_expiry': '2019-12-03', 'issuing_country': 'ITA'}MaleITApassport2019-12-03ITA   
5{'document_type': 'driving_licence', 'date_of_expiry': '2025-09-18', 'issuing_country': 'IRL'}  driving_licence2025-09-18IRL   
6{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2025-03-05', 'issuing_country': 'GBR'}Female driving_licence2025-03-05GBR   
7{'nationality': 'DEU', 'document_type': 'national_identity_card', 'date_of_expiry': '2026-01-03', 'issuing_country': 'DEU'} DEUnational_identity_card2026-01-03DEU   
8{'gender': 'Female', 'nationality': 'NLD', 'document_type': 'national_identity_card', 'date_of_expiry': '2017-06-12', 'issuing_country': 'NLD'}FemaleNLDnational_identity_card2017-06-12NLD   
9{'gender': 'Female', 'document_type': 'national_identity_card', 'date_of_expiry': '2020-08-20', 'issuing_country': 'ESP'}Female national_identity_card2020-08-20ESP   
10{'document_type': 'driving_licence', 'date_of_expiry': '2022-01-24', 'issuing_country': 'LTU'}  driving_licence2022-01-24LTU   
11{'gender': 'Male', 'nationality': 'PHL', 'document_type': 'passport', 'date_of_expiry': '2022-01-24', 'issuing_country': 'PHL'}MalePHLpassport2022-01-24PHL   
12{'gender': 'Male', 'nationality': 'GBR', 'document_type': 'passport', 'date_of_expiry': '2018-11-10', 'issuing_country': 'GBR'}MaleGBRpassport2018-11-10GBR   
13{'gender': 'Male', 'document_type': 'driving_licence', 'issuing_state': 'CA', 'date_of_expiry': '2020-03-23', 'issuing_country': 'USA'}Male driving_licence2020-03-23USA CA 
14{'gender': 'Male', 'nationality': 'IRL', 'document_type': 'passport', 'date_of_expiry': '2024-11-03', 'issuing_country': 'IRL'}MaleIRLpassport2024-11-03IRL   
15{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2019-06-12', 'issuing_country': 'GBR'}Female driving_licence2019-06-12GBR   
16{'gender': 'Female', 'nationality': 'ITA', 'document_type': 'passport', 'date_of_expiry': '2023-10-03', 'issuing_country': 'ITA'}FemaleITApassport2023-10-03ITA   
17{'gender': 'Male', 'nationality': 'ESP', 'document_type': 'national_identity_card', 'date_of_expiry': '2024-06-23', 'issuing_country': 'ESP'}MaleESPnational_identity_card2024-06-23ESP   
18{'document_type': 'driving_licence', 'date_of_expiry': '2017-08-23', 'issuing_country': 'SWE'}  driving_licence2017-08-23SWE   
19{'gender': 'Male', 'nationality': 'FRA', 'document_type': 'passport', 'date_of_expiry': '2024-08-05', 'issuing_country': 'FRA'}MaleFRApassport2024-08-05FRA   
20{'gender': 'Female', 'nationality': 'FRA', 'document_type': 'passport', 'date_of_expiry': '2023-11-13', 'issuing_country': 'FRA'}FemaleFRApassport2023-11-13FRA   
21{'gender': 'Female', 'nationality': 'ZAF', 'document_type': 'passport', 'date_of_expiry': '2026-03-15', 'issuing_country': 'ZAF'}FemaleZAFpassport2026-03-15ZAF   
22{'gender': 'Male', 'nationality': 'ESP', 'document_type': 'national_identity_card', 'date_of_expiry': '2019-04-28', 'issuing_country': 'ESP'}MaleESPnational_identity_card2019-04-28ESP   
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'}MaleAUTnational_identity_card2027-03-26AUT   
25{'gender': 'Male', 'nationality': 'BEL', 'document_type': 'national_identity_card', 'date_of_expiry': '2025-04-03', 'issuing_country': 'BEL'}MaleBELnational_identity_card2025-04-03BEL   
26{'gender': 'Male', 'nationality': 'BEL', 'document_type': 'national_identity_card', 'date_of_expiry': '2025-04-03', 'issuing_country': 'BEL'}MaleBELnational_identity_card2025-04-03BEL   
27{'gender': 'Female', 'nationality': 'GBR', 'document_type': 'passport', 'date_of_expiry': '2024-06-26', 'issuing_country': 'GBR'}FemaleGBRpassport2024-06-26GBR   
28{'gender': 'Female', 'nationality': 'MEX', 'document_type': 'passport', 'date_of_expiry': '2018-11-11', 'issuing_country': 'MEX'}FemaleMEXpassport2018-11-11MEX   
29{'gender': 'Female', 'nationality': 'MAR', 'document_type': 'passport', 'date_of_expiry': '2020-12-22', 'issuing_country': 'MAR'}FemaleMARpassport2020-12-22MAR   
30{'gender': 'Male', 'nationality': 'PRT', 'document_type': 'national_identity_card', 'date_of_expiry': '2019-11-23', 'issuing_country': 'PRT'}MalePRTnational_identity_card2019-11-23PRT   
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'}MaleTURpassport2023-09-29TUR   
List
Cell Formulas
RangeFormula
B2:I32B2=IFERROR(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID($A2,FIND(B$1,$A2)+LEN(B$1)+3,200),",",REPT(" ",100)),"}",""),100)),"'",""),"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,816
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,977
Members
417,120
Latest member
Pavithra devi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top