Solved: Insert character with VBA

[obed_cruz]

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.

[GTO]

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:

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

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

[lucas]

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.....

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

[obed_cruz]

Thanks Lucas and GTO,

Your help me has been very useful and I have solved my problem.

Greetings.

[lucas]

Welcome to the forum. You can mark your thread solved using the thread tools at the top of the page.