Text to Date and time

[Gusch]

I've looked around and could not find the solution. I know I've done something similar before, but it was a long time ago.

I need extract the date and time from the follow text string:

Jan 6 2019 8:30AM

Thanks

 

[Tetra201]

Assuming you have your string in cell A1, the following formulas will give you:

Excel date ad time =--SUBSTITUTE(SUBSTITUTE(REPLACE(A1,7,0,", "),"AM"," AM"),"PM"," PM")
Excel date =INT(SUBSTITUTE(SUBSTITUTE(REPLACE(A1,7,0,", "),"AM"," AM"),"PM"," PM"))
Excel time =MOD(SUBSTITUTE(SUBSTITUTE(REPLACE(A1,7,0,", "),"AM"," AM"),"PM"," PM"),1)