Regex Pattern

[orange13]

I have the following text in cell A1 and am trying to replace the -- (double dash) that appears before the 5 digits with a backslash.

My text in A1 looks like this: MD03_Cncr_Elev3_iv---172--00001.mp3

After replacing the -- it should look like this

MD03_Cncr_Elev3_iv---172\00001.mp3

The pattern will always be -- followed by five digits and a period.

This is the code I am using but the replace part is not correct as it replaces the entire pattern with a backslash.

Sub ReplaceDoubleDash()
Dim regex As Object
Dim myCell As Range
Set regex = CreateObject("VBScript.RegExp")
On Error Resume Next

regex.Global = True
regex.Pattern = "[-][-][0-9]{1,5}[.]"
For Each myCell In Selection.Cells
myCell.Value = regex.Replace(myCell.Value, "\")
Next

End Sub

Any help would be appreciated.

[Bernie Deitrick]

Until some Regex Expert comes along, try simple VBA:

Please Login or Register  to view this content.

As a side note, trying to nest the three Replace functions resulted in the code being blocked as a SQL injection attempt....

[jolivanes]

With a loop maybe.

Please Login or Register  to view this content.

[jolivanes]

This is what you had in mind Bernie?

Because "Securi" would not let me publish the code either, it is in the attached text file.

[jindon]

Change the pattern to

Please Login or Register  to view this content.

[orange13]

Thank you everyone. I ended up using the one from jindon as after some testing some of the text had more than 3 characters after the period.