Hello everybody,
My problem is:
I have in column A few dates with this format:
20081201
20080923
and what I need is to insert a separator with VBA between year, date and day and make it so.
2008-12-01
2008-09-23
Someone can help me.
Hello everybody,
My problem is:
I have in column A few dates with this format:
20081201
20080923
and what I need is to insert a separator with VBA between year, date and day and make it so.
2008-12-01
2008-09-23
Someone can help me.
Greetings Obed,
In a copy of your workbook (case I screw this up), if you select all the cells you want to change, this should work:
[vba]Sub ChangeToDates()
Dim rCell As Range
For Each rCell In Selection
rCell = Left(rCell, 4) & "-" & Mid(rCell, 5, 2) & "-" & Right(rCell, 2)
Next
Selection.NumberFormat = "yyyy-mm-dd"
End Sub[/vba]
This does assume that the current data is as you specified, that is two-digits for month and day and four digits for year.
Hope this helps,
Mark
This alternative works on all of column A with data.. the only caveot is that you have to have column B empty for the formula's. You can insert a temporary column B if you need to. Could be done with code.....
[VBA]
Sub AddDashes()
Dim LastRow As Long
Dim rng As Range
LastRow = Range("A65536").End(xlUp).Row
Set rng = Range("A1:A" & LastRow)
Range("B1").Formula = _
"=LEFT(A1,4) &" & Chr(34) & "-" & Chr(34) & "& MID(A1,3,2)& " & Chr(34) & "-" & Chr(34) & " &RIGHT(A1,2)"
Range("B1").Copy rng.Offset(0, 1)
rng.NumberFormat = "yyyy-mm-dd"
rng.Value = rng.Offset(0, 1).Value
rng.Offset(0, 1).ClearContents
End Sub
[/VBA]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Thanks Lucas and GTO,
Your help me has been very useful and I have solved my problem.
Greetings.
Welcome to the forum. You can mark your thread solved using the thread tools at the top of the page.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln