Google Sheets – How to Extract Time from Text

google sheetsgoogle-sheets-datesregex

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?

Best Answer

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