2

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.

enter image description here

Improve this question
1
  • Yogi, please share a link to a sheet containing exactly what you show in your post and in the same location (S1:W5), being sure to set the link's Share permission to "Anyone with the link can edit." As it stands, you'd be asking people here to spend time setting up our own sheet, manually typing in your example data, solving on our own sheet and then sharing our solution; whereas if you share a link to a sample spreadsheet, we can jump right in and leave suggested solutions direction on your sheet for you.
    – Erik Tyler
    Nov 30, 2020 at 22:12

1 Answer 1

Reset to default
1

You could try the following:

enter image description here

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'}
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.