Replace Text w/ VBA
 

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,

Hi,

You might want to try EditReplace and replace a "." with a "/".

Hope that helps.

Jason

"Jeff" wrote:

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,

Thank you,

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


"Excel heavy user" wrote:

Hi,

You might want to try EditReplace and replace a "." with a "/".

Hope that helps.

Jason

"Jeff" wrote:

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,

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


Jeff wrote:

Thank you,

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

"Excel heavy user" wrote:

Hi,

You might want to try EditReplace and replace a "." with a "/".

Hope that helps.

Jason

"Jeff" wrote:

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,


--

Dave Peterson