Getting irregular formatted date into usable date

[ch_ar_sh]

Hi all,

I've been trying to get a string in the format of 31st Aug 17 4:12pm AEST into a usable date format.

Things I've tried so far include but are not limited to the DATEVALUE function, =LEFT(O2,2) function, however not sure how to make sure when numbers go from single to double digit it pulls across.

Any help would be greatly appreciated.

<colgroup><col width="64"></colgroup><tbody>
</tbody>

 

[Jonmo1]

Try this assuming you don't need the time, and the mmm yy format is consistent.

=LEFT(REPLACE(A1,FIND(" ",A1)-2,2,""),9)+0

 

[JLGWhiz]

Something like

=Left(O2, Len(O2)-2)

If only the ordinal, if the entire date then

=Left(O2, Len(O2)-9)

 

[ch_ar_sh]

Thank you both for replying.

All working, really really appreciate it.

 

[Jonmo1]

You're welcome.