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

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 '20 at 22:12
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'}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.