Formula to Extract Text Between First and Third Space

[seaottr]

Hi all,

I know this is an easy one, and I'm clearly an idiot, but was hoping someone could help me figure out the formula to extract the characters between the first and third space.

Example:

A1 = "11/30/2017 02:23 PM America/Toronto"

Need to extract "02:23 PM"

Thank you in advance!

 

[snjpverma]

Deleted

 

[Scott T]

If you just need the text this will work

=MID(A1,SEARCH(" ",A1)+1,SEARCH(":",MID(A1,SEARCH(" ",A1)+1,999))+5)

If you need it to be a excel time then this will work. format the cell as time.

=TIMEVALUE(MID(A1,SEARCH(" ",A1)+1,SEARCH(":",MID(A1,SEARCH(" ",A1)+1,999))+5))

 

[seaottr]

Unfortunately that returns " 02:23" snjpverma

 

[seaottr]

Thanks Scott!!! You rock! And thank you for providing the solution for both text and time outputs!!!

 

[Rick Rothstein]

I know this is an easy one, and I'm clearly an idiot, but was hoping someone could help me figure out the formula to extract the characters between the first and third space.

Example:

A1 = "11/30/2017 02:23 PM America/Toronto"

Need to extract "02:23 PM"

Do your month and day values always have leading zeroes when they are single digits? If so...

=MID(A1,12,8)

for the time as a text value. If you want the time to be a real time value, just add zero to it...

=0+MID(A1,12,8)

If there might not always be a leading zero, then use this for the text time...

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,200))

and add zero to it for the real time value.