use macro to change dates

B

BigBird

I have never used macros before but am trying to convert dates using excel
2003. The dates are in the format of
+1.123.456.1234 and I would like to change that to
123-456-1234. There are also some listed as 123.456.1234 and would like
them to be the same as the first ones, that is, 123-456-1234.
I tried using the "start recording" method and "stop recording" but the
cursor just jumps back to the original cell when I try to use the recorded
macro. I have 2000 cells to change. Anybody have a suggestion on how to
change these? I looked at the VBE, but don't know what to change.
 
B

Bob Phillips

Sub TidyUp()
Dim iLastRow As Long
Dim i As Long

With ActiveSheet
Application.ScreenUpdating = False
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
.Cells(i, "A").Value = Replace(.Cells(i, "A").Value, ".", "-")
If Left(.Cells(i, "A").Value, 2) = "+1" Then
.Cells(i, "A").Value = Right(.Cells(i, "A").Value, _
Len(.Cells(i, "A").Value) - 3)
End If
Next i
Application.ScreenUpdating = True
End With
End Sub


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

Michael Bednarek

I have never used macros before but am trying to convert dates using excel
2003. The dates are in the format of
+1.123.456.1234 and I would like to change that to
123-456-1234. There are also some listed as 123.456.1234 and would like
them to be the same as the first ones, that is, 123-456-1234.
I tried using the "start recording" method and "stop recording" but the
cursor just jumps back to the original cell when I try to use the recorded
macro. I have 2000 cells to change. Anybody have a suggestion on how to
change these? I looked at the VBE, but don't know what to change.
Click to expand...

It can be done without a VBA macro using Replace (Ctrl+H).

Highlight the cells in question (optional).
1st:
Find: +1.
Replace: <nothing>
Then:
Find: .
Replace: -

In VBA:
Range("A:A").Replace "+1.", ""
Range("A:A").Replace ".", "-"
Qualify and modify Range() as needed.
 
Top