How do I extract specific value from a single cell on Google Sheets

Question

I'd like to get respective data into each cell without the key and punctuations. Each cell must display only the value extracted from the properties column.

I've tried to go to Data>split text to the column, but that does not solve my problem. I have around 5000 records. Please note that there is missing data in the properties column. In some cases the data will be blank.

Answer 1

You could try the following:

Formula in T2:

=ARRAYFORMULA(REGEXEXTRACT($S2:$S5,T1&"':\s'([^']+)"))

Dragged to W2.

---

For those who want to have a go with the data:

{'gender': 'Female', 'document_type': 'driving_licence', 'date_of_expiry': '2023-12-05', 'issuing_country': 'GBR'}
{'gender': 'Male', 'document_type': 'driving_licence', 'date_of_expiry': '2020-05-13', 'issuing_country': 'GBR'}
{'gender': 'Male', 'nationality': 'GBR',  'document_type': 'passport', 'date_of_expiry': '2023-11-15', 'issuing_country': 'GBR'}
{'gender': 'Male', 'nationality': 'PER',  'document_type': 'passport', 'date_of_expiry': '2021-01-27', 'issuing_country': 'PER'}