excel - Convert text date format to date format

Question

I have a date as text in this format - "22nd July 2016". How do I convert it into date format using Excel-VBA.

Thanks in advance.

Answer 1

One way is to just strip-off the ordinal indicator:

Public Function dateOR(s As String) As Date
    ary = Split(s, " ")
    ary(0) = Left(ary(0), Len(ary(0)) - 2)
    dateOR = DateValue(Join(ary, " "))
End Function

and without VBA you can use the worksheet formula:

=DATEVALUE(SUBSTITUTE(A1,MID(A1,FIND(" ",A1)-2,2),""))