Replace Text w/ VBA

J

Jeff

Hello,

I need help to designing a VBA macro that would replace in column K:
the following text "30.11.2004" into text "11/30/2004".
I need this macro to be as generic as possible since I may have other texts
such as 30.09.2004, etc.
Thanks,
 
E

Excel heavy user

Hi,

You might want to try Edit>Replace and replace a "." with a "/".

Hope that helps.

Jason
 
J

Jeff

Thank you,

I still need to inverse the month and the date. That's Why I need a VBA
Macro.
Regards,
 
D

Dave Peterson

If you only have to do this once, you could change your windows regional
settings to look like dmy, then do that edit|replace. (then change the regional
settings back to dmy.)

But as a macro:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range

Set myRng = Selection

'30.11.2004
For Each myCell In myRng.Cells
With myCell
.Value = DateSerial(Mid(.Value, 8, 4), _
Mid(.Value, 4, 2), _
Left(.Value, 2))
.NumberFormat = "mm/dd/yyyy"
End With
Next myCell
End Sub

This does assume that the input is all the same dd.mm.yyyy (10 characters).
 
Top