Convert inconsistenly formatted text to date

[smiley3141]

I receive text inconsitently formatted text strings that I would like to convert to a date. The strings always have the date followed by a message.

Unfortunately, the date comes in several different forms. The day can take one of four formats: not named, one letter, three letters, or spelled out. The month can take the following formats: three letters, three letters followed by period, four letters, four letters followed by a period, spelled out, 09, or 9. The year can take the following forms: not named, four number years, or two number years. The strings can have any combination of the above-listed formats. Some examples from yesterday are:
Friday, Sep. 24, 2010
Fri, Sep 24
F, September 24, 2010
9/24/10
Friday, 09/24/2010
Sept. 24, 2010

The date can be followed by nothing or a message. Some example messages might be:
Homeroom
Study Hall
Mrs. Q. Public's Policy Changes: 12-A Reg. 12(b)
(Ha!!!!!!)

If there is a message after the date, the date is always followed by at least one space.

My question: Is there some way to consintently trim the extraneous text and then format the rest as a date?

Thank you for any assistance you can offer me.

 

[shg]

Is a UDF OK?

Function SnagTheDate(ByVal sInp As String) As Date
    Dim i           As Long
    Dim j           As Long
    Dim asInp()     As String
    Dim sDate       As String
 
    sInp = WorksheetFunction.Trim(Replace(Replace(sInp, ",", " "), ".", " "))
    asInp = Split(sInp, " ")
    If UBound(asInp) > 3 Then ReDim Preserve asInp(3)
 
    For i = 0 To UBound(asInp)
        For j = UBound(asInp) To i Step -1
            sDate = Cat(asInp, i, j)
            If IsDate(sDate) Then
                SnagTheDate = CDate(sDate)
                Exit Function
            End If
        Next j
    Next i
End Function
 
Function Cat(asInp() As String, i As Long, j As Long) As String
    Dim k As Long
 
    On Error Resume Next
 
    For k = i To j
        Cat = Cat & " " & asInp(k)
    Next k
 
    Cat = WorksheetFunction.Trim(Cat)
End Function

Usage: =SnagTheDate(A1)

 

[smiley3141]

Thanks a lot, Shg4421.

I have tested it on a couple dozen variations in formats and it has worked correctly on each one.

Thank you, once again.