I have the following issue and I would appreciate your help. I need to get the time values out of this string:

SomeText 02/02/2020 9:00 AM-02/02/2020 9:15 AM;"Text" 02/02/2020 10:45 AM-02/02/2020 11:15 AM;"Text" 02/02/2020 12:45 PM-02/02/2020 1:00 PM;

The pattern and length are not consistent. But time always comes after the date.

Any suggestions?


To get the time out of timestamps found in mixed text, try this formula
(where A2 is the cell with our text):

=SPLIT(REGEXREPLACE(REGEXREPLACE(A2,"\D|(../../.... (.:.\d|..:..) (PM|AM))|[0-9]","♜$1"),"♜|(../../.... )","♞"),"♞",1,1)

Functions used:

Mention: I noticed that you also posted your question on stackoverflow as well where you also got another variation of a working solution.
So to use the best of both worlds (shorter, returns a number a well instead of a text), you can try this:

  • Please remember that as per site guidelines when an answer addresses your question, accept it so others can benefit as well. – marikamitsos Feb 23 '20 at 18:32
  • Thank you so much! Works perfectly – Murry Feb 24 '20 at 11:34
  • Great. Try the new formula as well. – marikamitsos Feb 24 '20 at 14:45

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.