Hi – I need some assistance in extracting information from a dynamic string, either using VBA or formulas. In one column is an “id” and the adjacent column has a string of “Sale Types” – each Sale Type has a date/time and a title. I want to extract a date and time from the Sale Type that meet these conditions:
- Sales Type name contains “STARTING”
- If multiple names containing “STARTING” are found, only output the earliest date
One example Sales Type string:
[{"date":"2020-01-17T23:00:00Z","name":"Official STARTING"},{"date":"2020-01-17T23:00:00Z","name":"VIP Package Onsale"}{"date":"2020-01-19T23:00:00Z","name":"STARTING Onsale"}]
In the example above, there are two Sales Types with STARTING in the name, but I just want the earliest date, so my output would be: 1/17/2020 3:00 pm
I’ve also attached a sample file with more examples if that helps. Note on the times (3:00pm, etc.) - I’m unsure of the exact time zone the strings are using, but I can always convert time zone later as long as the data is extracted.
Thank you so much for your time!