Extract Date and time from the Text Column

[Rick Rothstein]

Just realized that date and time is not always at the end...

Well, that changes everything. Assuming your time values always have either an AM or PM designation, give this formula a try...

=IFERROR(0+SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(LEFT(A1,IFERROR(FIND(" PM",A1),FIND(" AM",A1)))," ",REPT(" ",300)),1500),1500)),",","",2),"")

[zorro]

It works, just that it is picking the time only in AM, even if it is PM

[Rick Rothstein]

It works, just that it is picking the time only in AM, even if it is PM

Sorry, I forgot to add 2 in order to retain the AM/PM designation. Here is the revised formula....

=IFERROR(0+SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(LEFT(A1,IFERROR(FIND(" PM",A1)+2,FIND(" AM",A1)+2))," ",REPT(" ",300)),1500),1500)),",","",2),"")