How to convert time stamp with text into a date

Question

My data is extracting to this format May 22 2020 6:29PM

How do you convert this into 22/05/2020?

I have gotten the formula to this point =+LEFT(E12,LEN(E12) - FIND(" ",E12)-4) but cannot get it any further.

Answer 1

Try the following:

=DATEVALUE(SUBSTITUTE(LEFT(E12,FIND("~",SUBSTITUTE(E12," ","~",3)))," ",", ",2))

and apply the date format dd/mm/yyyy to the cell:

Note that this discards the time portion of the original date/time.