How to extract time out of text in Google Sheets

Question

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?

Answer 1

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:

• REGEXREPLACE
• SPLIT

---

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"),"♜")