Excel VBA Find & Replace Text In A String With Loop

Question

I'm trying to replace all the text in a string between the pattern "&CC[number]:[number]" and replace it with a "==".

Here is the string. "T &CC3:5 Q8 Party/ Self-Identify&CC6:8 Male&CC9:11 Female&CC12:15 Q1 Vote"

This is what I need it to look like T &CC3:5==&CC6:8==&CC9:11==&CC12:15==

I know I need to loop through this string but I'm not sure the best way to set this up.

Dim stringOne As String
Dim regexOne As Object
Set regexOne = New RegExp

regexOne.Pattern = "([Q])+[0-9]"
regexOne.Global = False
stringOne = "T &CC3:5 Q8 Party/ Self-Identify&CC6:8 Male&CC9:11 Female&CC12:15 Q1 Vote"

Debug.Print regexOne.Replace(stringOne, "==")
End Sub

I have also explored using this regular expression regexOne.Pattern = "([&])+[C]+[C]+[0-9]+[:]+[0-9]"

I plan to eventually set the variable stringOne to Range("A1").Text

If a positive lookahead is supported you could use `(&CC[0-9]+:[0-9]+).*?(?=&|$)` and replace with the first capturing group and == https://regex101.com/r/5o8QVE/1 — The fourth bird, Mar 03 '20 at 18:56
Does this answer your question? [How to extract text within a string of text](https://stackoverflow.com/questions/7086270/how-to-extract-text-within-a-string-of-text) — urdearboy, Mar 03 '20 at 18:58
@Thefourthbird that would work for sure. How does the positive lookahead work? I have some questions that have double ampersand that break the regex. See https://regex101.com/r/5o8QVE/2 Would there be a way to account for that? — Seth, Mar 03 '20 at 19:07
You could add matching a C after it https://regex101.com/r/z50WZq/1 — The fourth bird, Mar 03 '20 at 19:11
Thank you @Thefourthbird! You are amazing with regex. I need to learn more. — Seth, Mar 03 '20 at 19:13
@Seth You are welcome, I have updated the answer with an explanation. — The fourth bird, Mar 03 '20 at 19:15

The fourth bird · Accepted Answer · 2020-03-03T19:15:26.447

You could simplify the pattern a bit and use a capturing group and a positive lookahead

(&CC[0-9]+:[0-9]+).*?(?=&C|$)

Explanation

( Capture group 1
- &CC[0-9]+:[0-9]+ Match &CC 1+ digits, : and 1+ digits
) Close group
.*? Match 0+ times any char except a newline non greedy
(?=&C|$) Positive lookahead, assert what is directly on the right is either &C or the end of the string

Regex demo

In the replacement use the first capturing group followed by ==

Excel VBA Find & Replace Text In A String With Loop

1 Answers1