Excel: text conversion to date format

Question

I would like to convert text - Apr 7 2017 into date format on Excel spread sheet. Is there a formula that might help with it? I have already tried format cell into date format but this did not change the cell properties. Thank you for your suggestions

Answer 1

With data in A1, in another cell enter:

=DATEVALUE(LOOKUP(LEFT(A1,3),{"Apr","Aug","Dec","Feb","Jan","Jul","Jun","Mar","May","Nov","Oct","Sep"},{4,8,12,2,1,7,6,3,5,11,10,9}) & MID(SUBSTITUTE(A1," ","/"),4,99))

An alternative:

=DATEVALUE(TRIM(MID(A1,5,2)) & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))