string Date with day of week into mm/dd/yyyy in VBA

[Rfcolon428]

I have a column with the following info in a range of cells

Tue Mar 05 2019, I was looking at some examples for CDate, but I have the day of week in there as well. I think I would need to delete the first 3 char and then apply Cdate. How would I convert that to 03/05/2019?

<tbody>
</tbody>

 

[Rick Rothstein]

I have a column with the following info in a range of cells

Tue Mar 05 2019, I was looking at some examples for CDate, but I have the day of week in there as well. I think I would need to delete the first 3 char and then apply Cdate. How would I convert that to 03/05/2019?

<tbody>
</tbody>

Is that comma after 2019 actually in the cell?

What range of cells are these text dates in... a single column, a single row or a two-dimensional range of cells? Also, can you tell us the address for whichever it is?

Your posted example shows two space characters between the month and day number... is that actually what is in your cells?

 

[JoeMo]

Assuming the text date is in A1:

[b1] = CDate(Right([a1], Len([a1]) - 4))
[b1].NumberFormat = "mm/dd/yyyy"

 

[Rfcolon428]

No comma after 2019.

A single column
I2: lastrow

 

[Rick Rothstein]

Your use of CDate seemed to indicate you wanted a macro. Give this one a try...

Sub Macro1()
  With Range("I1", Cells(Rows.Count, "I").End(xlUp))
    .TextToColumns , xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(4, 3))
    .NumberFormat = "mm/dd/yyyy"
  End With
End Sub