Convert text to date

[Excelllllllllllllllllllll]

Hello,

There's a report at work that returns dates in the below format. I'm wondering if there's an automatic way to convert those into dates, in a helper column.
Either formula or VBA, I don't mind.

17TH AUG

31ST AUG

14TH SEP

28TH SEP

 

[Excelllllllllllllllllllll]

Solved.

Using the format from the above comment I created a helper column B and put in the formula:
=VALUE(LEFT($A1,SUM(LEN($A1)-LEN(SUBSTITUTE($A1,{"0","1","2","3","4","5","6","7","8","9"},""))))&"-"&RIGHT($A1,3))

 

[Rick Rothstein]

I believe this formula will also work...

=0+REPLACE(A1,LEN(A1)-5,2,"")

If it doesn't work because of locale differences, then this one will work (it creates the same date text string that your formula does)...

=0+REPLACE(A1,LEN(A1)-5,2,"-")