Regex Challenge: Selectively removing the same word from string?

Hi everyone,

Anyone know how to do this via regex? I would like to ONLY keep instances of the word Region that end in either a comma or nothing at all.

Example of what Id' like to remove in Red and what I'd like to keep in Blue.

Svc:Network,Region:Aspac,Country:China,Region,Pm:Jerry Mao, Region:EMEA, Region

As we can see, Region shows up twice.. but Id' like to treat the word "Region" as a separate entity in my output so just removing anything after the Word Region would not work.

In others remove "Region:Aspac" and "Region:EMEA" values but keep all instances of where ONLY Region shows up.

region_remove.PNG

Tips and Tricks

Parse

Expression

Interface Tools

Accepted answers

NicoleJohnson

I might have a solution, I think I understand now what you're looking for... sounds like a RegEx_CountMatches scenario (or you could skip RegEx altogether and go with Contains).

If REGEX_CountMatches([Field1],"Region:") > 1 then null()
Elseif REGEX_CountMatches([Field1],"Region") > 1 then "Region"
else null() endif

If Contains([Field1],"Region:") then null()

Elseif Contains([Field1],"Region") then "Region"

Else null() endif

Sometimes, though I hate to admit this, RegEx is not always the easiest answer Play around with it some more - RegEx101.com is a great resource for practicing various scenarios to determine if your syntax will work!

Cheers,

All comments

LordNeilLord

Hey @waqaraphoto

Using regex replace you can use the following Regex: (Region\:\w.*?,)

RegEx Replace.PNG

RegRep.yxmd

fmvizcaino

Hi waqaraphoto,

Try using this expression in Regex with Replace output method.

Region:.*?,

Regex community.JPG

Best,

Fernando V.

NicoleJohnson

Try this formula!

REGEX_Replace([Field1],"Region:\w*,+","")

Because it is specifically calling out to replace only Region when it is followed by a colon & other letters (and an optional comma), you should still see any instances of Region that you're aiming to keep. (Note that this might give you some duplicate whitespace that you'd want to remove with another Replace formula or a Data Cleanse tool.)

Hope that helps!

Cheers,

waqaraphoto

Hi Nicole,

I'm seeing all instances of the instances of the word "Region" that I do wish to keep.. But I believe I'm still also seeing those which are followed by :text.

This is a browse output at the end of the formula tool containing the formula.

(Append Region is the column Id' like to show instances of the word Region Only. As we can see we're seeing the unwanted version as well.)

Thanks for looking into this!

append region.PNG

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703