Changing part of the Date String

[keith05281967]

Greetings,

I have a string var that contains a file name "FILENAME_06052011"

If the file name meets a certain criteria, I want to change the "day part" , MMDDYYYY in the date to the 1st day of the month.

So "FILENAME_06052011" would become "FILENAME_06012011"

This is "VBA CODE" i'm talking about. Not a worksheet function

Is there a function in VBA that will help me? Like Right stmt? Can this be used inside a Replace?

Excel 07
thanks,
Keith

 

[VoG]

Try like this

Sub cccc()
Dim s As String
s = "FILENAME_06052011"
s = Left(s, InStr(s, "_") + 2) & "01" & Right(s, 4)
MsgBox s
End Sub

 

[Rick Rothstein]

Using VoG's setup as a basis, here is another way to do what you want...

Sub cccc()
    Dim s As String
    s = "FILENAME_06052011"
    Mid(s, Len(s) - 5, 2) = "01"
    MsgBox s
End Sub

Using Mid in statement form (as above) is extremely fast in VB although if you are not executing this in a very large loop, you won't see its speed advantage over the originally proposed concatenation method.

 

[keith05281967]

Those both work brilliantly! thank you so much.

Keith

 

[shg]

If you're using all of the string on the right, you need not specify the length to the function:

Mid(s, Len(s) - 5) = "01"

 

[Rick Rothstein]

If you're using all of the string on the right, you need not specify the length to the function:

Mid(s, Len(s) - 5) = "01"

That's true.... good point (although some may not consider it as clear a statement without the length argument).