Parsing data into columns

kingwombat · Apr 6, 2020

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': '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 · Apr 6, 2020

Is this information already on a worksheet or where?

kingwombat · Apr 6, 2020

yes, It's in a worksheet

Fluff · Apr 6, 2020

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'}

4

{'gender': 'Male', 'nationality': 'ITA', 'document_type': 'passport', 'date_of_expiry': '2019-12-03', 'issuing_country': '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'}

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'}

12

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

13

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

14

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

15

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

16

{'gender': 'Female', 'nationality': 'ITA', 'document_type': 'passport', 'date_of_expiry': '2023-10-03', 'issuing_country': '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'}

20

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

21

{'gender': 'Female', 'nationality': 'ZAF', 'document_type': 'passport', 'date_of_expiry': '2026-03-15', 'issuing_country': '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'}

28

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

29

{'gender': 'Female', 'nationality': 'MAR', 'document_type': 'passport', 'date_of_expiry': '2020-12-22', 'issuing_country': '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'}

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 · Apr 6, 2020

Thank you very much! this works

Fluff · Apr 6, 2020

You're welcome & thanks for the feedback

Parsing data into columns

kingwombat

New Member

Excel Facts

Anthony47

Well-known Member

kingwombat

New Member

Fluff

MrExcel MVP, Moderator

kingwombat

New Member

Fluff

MrExcel MVP, Moderator

Forum statistics

Share this page

Parsing data into columns

kingwombat

New Member

Excel Facts

Anthony47

Well-known Member

kingwombat

New Member

Fluff

MrExcel MVP, Moderator

kingwombat

New Member

Fluff

MrExcel MVP, Moderator

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock