Extract a string from a "properties" field

[davidwabel]

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 

[#2 · Accepted solution]

@davidwabel 

A quick solution with PQ.

[#3]

@Detlef Lewin 

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 

[#4]

@Detlef Lewin 

I think you mean Power Query? Is this available on Mac Excel 2016 ? 

-d 

[#5]

@davidwabel 

No, PQ for Excel on Mac is still in development.

[#6]

@Detlef Lewin 

Thanks

Any other method ?

-d

[#7]

@davidwabel 

The next best thing would be VBA. But that is not my expertise.