Extract simple time data from a string-CodePudding

I have an export of shift data from which I am looking to pull the start and end times so I can create a validation.

Below is a text version of four sample cells from the export. The dates don't matter, just looking to get something like hh:mm-hh:mm out of this.

In Excel I'd probably just do a series of left/right/find by trial and error.

Open_115636 09/08/2022 9:30 AM-09/08/2022 6:00 PM
Open_066513 09/08/2022 7:00 AM-09/08/2022 4:00 PM
Open_066513 09/08/2022 8:00 AM-09/08/2022 4:30 PM
Open_115636 09/08/2022 10:00 AM-09/08/2022 6:00 PM

CodePudding user response：

try:

=ARRAYFORMULA(IFNA(REGEXREPLACE(A1:A10, 
 ".  (\d :\d ). (-). (\d :\d ) . ", "$1$2$3")))

CodePudding user response：

Try the following

=INDEX(REGEXREPLACE(A1:A5, 
        ".  (\d :\d  \D -).  (\d :\d  \D )", "$1$2"))