1

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?

1 Answer 1

0

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:

=SPLIT(REGEXREPLACE(B5,"\D|(\d+:\d+\s[AP]M)|[0-9]","♜$1"),"♜")
3
  • Please remember that as per site guidelines when an answer addresses your question, accept it so others can benefit as well. Feb 23, 2020 at 18:32
  • Thank you so much! Works perfectly
    – Murry
    Feb 24, 2020 at 11:34
  • Great. Try the new formula as well. Feb 24, 2020 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.