convert text date with day of week abbreviation to

Question

I am trying to figure out how to remove the abbreviation for the day of the week from cells and then convert the text string date to an excel date format. This is an example of the format which will always be the same.

Thu 8/24/2017

If I manually delete the day of week letters and then use the text to columns function in excel, it works fine converting into a date format. The text to columns function does not work on cells containing the day abbreviation.

I am looking for the quickest method (VBA or formula) to remove these abbreviations from multiple columns. Find and replace with "" will be an option, but I am hoping for something less manual and time consuming, as I will be working with this spreadsheet regularly. Thank you in advance for your help!

Answer 1

Use:

=--MID(A1,FIND(" ",A1)+1,LEN(A1))

Then format it any way you want

Answer 2

You can do this in-place by ignoring the first field in Text-to-Columns.

With Worksheets("sheet3")
    With .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
        .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
            FieldInfo:=Array(Array(1, 9), Array(2, 3))
    End With
End With

Answer 3

This will transform your text into an excel date format:

=DATEVALUE(MID(B2,4,10))

"Thu 8/24/2017" will become "42971" under a general format, "8/24/2017" under a short date format or "Thursday, August 24, 2017" under a long date format.