Regex Find and Replace in String

Question

I have a few strings that look like the following:

xml=<data><optional><Account>192</Account></optional></data>, submitter=Q102, target=Q10, escalationType=esc, escalationReason=277, feedback=cx req live esc to have us review, adv cx his account is pending, preventable=0,

What I am trying to do is write a regular expression in excel (vb) to find all the text between feedback= and , preventable and replace all , in that with a PIPE instead.

This is the expression I have so far to find that text:

(?<=feedback=)(.*)(?=, preventable)/gi

However, I'm not too sure what to do at this point to replace text within that selection?

Here is the expected string after it has ran:

 xml=<data><optional><Account>192</Account></optional></data>, submitter=Q102, target=Q10, escalationType=esc, escalationReason=277, feedback=cx req live esc to have us review| adv cx his account is pending, preventable=0,

Here is a link to the Regex I am working with in my test: https://regex101.com/r/wR1dL0/2

If there will always have two phrases, then it could help: regex101.com/r/wR1dL0/3 — user4227915
– user4227915, Commented Jul 16, 2015 at 16:28
Sadly it could be a paragraph long with commas in any part of it so I'm not sure if its even possible with one epression — SBB
– SBB, Commented Jul 16, 2015 at 16:29
You probably want to use non-greedy .+? there. But, normally when you want to do two separate things in a single replace function, you would use a callback functionality. For example, in the replacement portion, specify a function that returns a replacement. In that function, take all from capture group 1, replace , -> |, then return the result. — user557597
– user557597, Commented Jul 16, 2015 at 16:39
A problem with your regex: VBScript Regular Expressions does not support look behind or mode modifiers — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 16, 2015 at 18:06

Ron Rosenfeld · Accepted Answer · 2015-07-17 03:21:35Z

At regex101 you have selected the PCRE flavor, but if you want to use VBA, you need to select the javascript flavor. In particular, look-behinds are not valid; nor are mode modifiers, in vbscript (javascript).

A regex that will match as you require would be:

,(?!.*feedback)(?=.*,\s*preventable)

The replacement string would merely be the PIPE

A UDF implementing that in VBA might be:

Option Explicit
Function CommaToPipeBetween(S As String, StartAt As String, EndAt As String) As String
    Dim RE As Object
    Dim sPat As String

sPat = ",(?!.*" & StartAt & ")(?=.*,\s*" & EndAt & ")"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = sPat
    .ignorecase = True

    CommaToPipeBetween = .Replace(S, "|")

End With

End Function

The UDF assumes that you do not want to replace a comma just before EndAt if it is separated by 0 or more space characters. But you could change the pattern to make things more generalized, if you want.

Collectives™ on Stack Overflow

Regex Find and Replace in String

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related