View Full Version : Solved: Insert character with VBA
obed_cruz
12-23-2008, 03:27 PM
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:
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
12-23-2008, 04:23 PM
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
12-23-2008, 05:08 PM
Thanks Lucas and GTO,
Your help me has been very useful and I have solved my problem.
Greetings.
lucas
12-23-2008, 05:20 PM
Welcome to the forum. You can mark your thread solved using the thread tools at the top of the page.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.