Apr 29 2020 05:15 PM - edited Apr 29 2020 05:25 PM
Hello Excel experts
I'm struggling with an 'extract' type of problem.
Basically I need to extract a string of unknown length from a properties field.
The string may or may not be present in the field.
The way I know the string is present is if it is preceded by a property string.
The property may or may not be present in the field.
For example, I need to extract Female if gender exists, or driving_license if document type exisits.
What I'm trying to do is to create a set of a gender columns that has the values from this compound field: a gender column, a nationality column, a document_type column, a date_of_expiry, issuing_country columns that may or may not have the extracted values.
Issuing date, issuing country, and date of expiry values are standard lengths so these would be easier but the document_type values can vary in length.
Below is some sample data records of the property field:
{'gender': 'Female', 'nationality': 'PRT', 'document_type': 'national_identity_card', 'date_of_expiry': '2019-11-13', 'issuing_country': 'PRT'}
{'document_type': 'national_identity_card', 'date_of_expiry': '2020-08-24', 'issuing_country': 'PRT'}
{'gender': 'Male', 'nationality': 'LTU', 'document_type': 'passport', 'date_of_expiry': '2026-01-12', 'issuing_country': 'LTU'}
{'gender': 'Female', 'nationality': 'CZE', 'document_type': 'national_identity_card', 'date_of_expiry': '2023-08-26', 'issuing_country': 'CZE'}
{'gender': 'Female', 'issuing_date': '2012-05', 'document_type': 'national_identity_card', 'issuing_country': 'FRA'}
{'gender': 'Male', 'document_type': 'driving_licence', 'date_of_expiry': '2019-07-09', 'issuing_country': 'GBR'}
{'gender': 'Male', 'issuing_date': '2010-12', 'document_type': 'national_identity_card', 'issuing_country': 'FRA'}
{'gender': 'Male', 'document_type': 'driving_licence', 'date_of_expiry': '2026-04-25', 'issuing_country': 'GBR'}
{'gender': 'Male', 'nationality': 'GBR', 'document_type': 'passport', 'date_of_expiry': '2025-01-25', 'issuing_country': 'GBR'}
{'gender': 'Male', 'nationality': 'ESP', 'document_type': 'national_identity_card', 'date_of_expiry': '2022-04-18', 'issuing_country': 'ESP'}
Thanks!
-d
Apr 29 2020 05:54 PM
SolutionApr 29 2020 08:51 PM
Thanks!
Can you describe how you did this ?
Ideally I need the output into discreet columns next to each source field, gender, nationality, etc. as opposed to repeating rows.
-d
Apr 29 2020 08:58 PM
Apr 29 2020 11:10 PM
Apr 30 2020 12:02 PM
Apr 30 2020 04:11 PM
Apr 29 2020 05:54 PM
Solution