string manupulation w/ Replace function 4

[johngrg]

0000100008+00009+0007+00000000000001 0000+10000"

For above string, I'd like to replace the 2nd '+' sign
with '-' and leave the rest of + sign as it is in the same format. How would I be able to do this? Thx

 

[EBGreen]

Well, your options depend on how consistent your data is. Will it always be structured the same way? Will there always be the same number of digits? Will the + that you want to replace always be at the same place?


The more consistent the data, the more specific and easy the solution is. The less consistent the data, the more generic and complex the solution needs to be.


[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Swi]

This method doesn't use the replace function but it should be dynamic enough to do what you want. It could probably also be done with less code.

Dim aryTemp
Dim i
Dim Test
Test = "0000100008+00009+0007+00000000000001   0000+10000"
aryTemp = Split(Test, "+")
Test = ""
Test = aryTemp(0) & "+" & aryTemp(1) & "-"
For i = (LBound(aryTemp) + 2) To UBound(aryTemp)
    Test = Test & aryTemp(i) & "+"
Next
Test = Left(Test, Len(Test) - 1)
MsgBox Test

Swi

 

[strongm]

This is a classic scenario for using a regular expression:

[blue]With New RegExp
    .Pattern = "(.*?\+.*?)(\+)"
    MsgBox .Replace("0000100008+00009+0007+00000000000001   0000+10000", "$1-")
End With[/blue]

 

[Swi]

As usual, nice use of regular expressions strongm. Have a star.

Swi

 

[johngrg]

Great! both solutions are very helpful.

If possible, can you pls explain the pattern so I can use
for other scenarios?

.Pattern = "(.*?\+.*?)(\+)"

Thx.

 

[strongm]

Ok, step 1:

http://msdn.microsoft.com/en-us/library/6wzad2b2(VS.85).aspx

Step 2 (ignoring the parenthesis):

.*: match any character 0 or more times
? : but don't be greedy. Use the minimal amount of characters possible to make the match.
\+: match a +
.*: match any character 0 or more times
? : but don't be greedy. Use the minimal amount of characters possible to make the match.

In other words try an match any pattern of text up to and including a second + (but no more than that)

OK, now for the parenthesis. We use these to group together part of the match and to be able to refer to that partial match later. Each bracketed section gets a name: $1, $2, $3, $4 ... which we can refer to in our replace method

So, assuming mattach is found, in this example $1 refers to the match up to but not including the second +, and $2 would refer to the + itself

Then in the replace method we replace the matched string (everything up to and including the second +) with $1 (everything up to but not including the second +) followed by -. ANything subsequent to the second + remains untouched

This has the required effect of replacing the second + with -