Extract specific information from a dynamic string

  • 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!:)

    Files

    • OZGRID.xlsx

      (10.93 kB, downloaded 56 times, last: )
  • Hello,


    Quick question about the structure of your strings and the sequence of dates ...


    Whenever the word STARTING multiple times, is the earliest date consistently in the " first " position of not ...


    i.e by design ...are the dates systematically appearing in ascending order ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • So ...


    Attached would be the first attempt ...:)


    Let me have your comments

    Files

    • Test Liz.xlsx

      (12.09 kB, downloaded 51 times, last: )

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Just added your Date Format ...


    Regarding the Zulu time ... seems that there are differences ...8o

    Files

    • Test Liz.xlsx

      (12.63 kB, downloaded 49 times, last: )

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Wow! What a formula...I don't know if I understand it but it looks correct! Haha...This is fantastic...thanks so much for your help and for doing it so quickly!

    Glad this could help you out ...:)


    Are you actually getting the results you were expecting ... :/


    Thanks for your Thanks ... AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Glad to hear it is working fine ...:)


    Do not hesitate to come back to the Forum ... should you need it ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)