Regex String Replacement

Question

I am using VBA to filter records in an MS Access Database I first need to clean the data in a particular column

An example item in this column would be XXXZZZSTYS1234NCYORGxxxASD

What i would like to do is if the string contains STY then remove subsequent characters until you reach COYORGxxx or NCYORGxxx or CNYORGxxx and then remove any characters after the ORGxxx if they exist

So in the example above it would be cleaned to XXXZZZSTYNCYORGxxx

It doesn't have to be VBA, I use notepad++ to test it

Have you tried anything yet?

Jerry
– Jerry

2014-05-15 12:08:35 +00:00
Commented May 15, 2014 at 12:08 — Jerry
– Jerry, Commented May 15, 2014 at 12:08

Renaud C. · Accepted Answer · 2014-05-15 12:16:24Z

1

You could try the following regex:

(.*?STY).*?((COY|NCY|CNY)ORGxxx).*

And replace it with:

$1$2

Where:

$1 represents all characters until the first "STY"
$2 represents COYORGxxx or NCYORGxxx or CNYORGxxx

answered May 15, 2014 at 12:16

Renaud C.

5252 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kilazur · Accepted Answer · 2014-05-15 12:16:32Z

1

There you go.

(.*STY).*((CO|NC|CN)YORGxxx).*

(.*STY) will catch every string of characters up to STY
.* will skip characters until next catch
((CO|NC|CN)YORGxxx) will catch any string starting with CO, NC or CN followed by YORGxxx
and the last .* is just here to ensure the match (not necessary in this specific example)

You then only need to replace with $1$2, $n representing the n-th catch of the regex.

answered May 15, 2014 at 12:16

Kilazur

3,1871 gold badge25 silver badges52 bronze badges

Collectives™ on Stack Overflow

Regex String Replacement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related