Convert MMM-DD-YYYY text to date

Question

I have a set of data which only pulls as MMM-DD-YYYY. I'd like to convert it to a date (MM/DD/YYYY format) to look it up versus another set of data.

I recorded a macro to simply replace the months with their respective numbers individually but I know there has to be a better way to do this. Below is my

current code:

With ws1.Cells
.Replace What:="jan-", Replacement:="01-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="feb-", Replacement:="02-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="mar-", Replacement:="03-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="apr-", Replacement:="04-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="may-", Replacement:="05-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="jun-", Replacement:="06-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="jul-", Replacement:="07-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="aug-", Replacement:="08-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="sep-", Replacement:="09-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="oct-", Replacement:="10-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="nov-", Replacement:="11-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
.Replace What:="dec-", Replacement:="12-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End With

Answer 1

This will convert your text string into a true date for the active cell:

Sub datefix()
    Dim s As String
    s = ActiveCell.Value
    arr = Split(s, "-")
    ActiveCell.Value = arr(1) & " " & arr(0) & " " & arr(2)
End Sub

You can format it or loop it to your heart's content.

(I am using US locale)

EDIT#1:

With your desired format:

Sub datefix()
    Dim s As String
    s = ActiveCell.Value
    arr = Split(s, "-")
    ActiveCell.Value = arr(1) & " " & arr(0) & " " & arr(2)
    ActiveCell.NumberFormat = "mm/dd/yyyy"
End Sub

Before:

and after: