Reformatting Strings

[SBF12345]

I would like to be able to reformat a date string in a workbook. The strings are in the format "15-Feb-08" for instance and I would like to have them read "02/15/2008". How can I use the hyphens as the basis to separate date components and reconstruct the date in a different format?

Thanks

 

[sandy666]

=--substitute(a1,"-","/") and format as Date
or
Text2Columns
Delimited - Next - Next - Date (MDY) - Finish

 

[SBF12345]

my mistake, I am writing this in VBA

Thus far I have, but I don't think MID is the correct function because some of the day strings are 1 character while others are 2

Dim YEAR As StringDim MONTH As String
Dim DAY As String
Dim A As Range
Dim B As Range
Dim TotalCells As Range


Set A = ActiveWorkbook.ActiveSheet.Range("A8")
Set B = ActiveWorkbook.ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
    
Set TotalCells = ActiveWorkbook.ActiveSheet.Range(A, B)


Range("B1").Select
Selection.EntireColumn.Insert


For Each C In TotalCells


    YEAR = Mid(ActiveCell, 3, 2)
    MONTH = Mid(ActiveCell, 6, 3)
    DAY = Mid(ActiveCell, 9, 2)


    


    If MONTH = "Jan" Then MONTH = "01"
    If MONTH = "Feb" Then MONTH = "02"
    If MONTH = "Mar" Then MONTH = "03"
    If MONTH = "Apr" Then MONTH = "04"
    If MONTH = "May" Then MONTH = "05"
    If MONTH = "Jun" Then MONTH = "06"
    If MONTH = "Jul" Then MONTH = "07"
    If MONTH = "Aug" Then MONTH = "08"
    If MONTH = "Sep" Then MONTH = "09"
    If MONTH = "Oct" Then MONTH = "10"
    If MONTH = "Nov" Then MONTH = "11"
    If MONTH = "Dec" Then MONTH = "12"


Cells(C, 2).value = MONTH & "/" & DAY & "/" & YEAR


Next C

 

[mikerickson]

Or you could use the DATEVALUE function.
=DATEVALUE(A1) for a worksheet formula or
if you want VBA

Sub test()
    Dim newData As Variant, i As Long
    With Sheet1
        With .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
            newData = .Value
            For i = 1 To .Cells.Count
                newData(i, 1) = DateValue(CStr(newData(i, 1)))
            Next i
            .Value = newData
            .NumberFormat = "mm/dd/yyyy"
        End With
    End With
End Sub

 

[SBF12345]

Great, it works, I would not have thought to handle the values as an array.

 

[Rick Rothstein]

Great, it works, I would not have thought to handle the values as an array.

You can also do what you want with looping...

Sub Test()
  With Range("A8", Cells(Rows.Count, "A").End(xlUp))
    .TextToColumns [A8], Other:=False, FieldInfo:=Array(1, 3)
    .NumberFormat = "mm/dd/yyyy"
  End With
End Sub