Regex words before and after set of common characters

adrrs

Hi community,

I have a regex query,and am unable to get my head around it. Might be simple for you guys.

I have a column which looks like so:

column

ABC Oy Apple Road

DE AF Ltd. Band Road

A DE Oy Col Street

GRE Plc Main Street

I would like to remove all characters before Oy/Ltd./Plc including these so that all that is left is the address (Apple Road, Band Road....).

Thanks a lot!

Rohit

Regex

Accepted answers

Thableaus

@adrrs

You could try something like this, but be careful

REGEX_Replace([Field1], ".*(?:\bLtd\b|\bOy\b|\bInc\b|\bPlc\b)\W*(.*)", "$1")

This probably might do the trick, if you see other words as patterns, you could include in the alternate expression.

Cheers,

All comments

Thableaus

Hi @adrrs

REGEX needs a pattern.

Is your pattern 3 characters, space, and then your address?

If yes, you don't even need REGEX for that.

A String function would do it.

Right(Field, Length(Field) - 4)

Cheers,

adrrs

Hi,

Not exactly. It was just an example but a real world example would be more like so:

Apple Inc. Some Road

General Motors Plc. Detroit St.

The point is that I have identified these Inc/Plc/Oy as the common characters in all observations that can enable me to split the variable so that I aonly retain what comes after these characters.

Thanks!

Thableaus

@adrrs

You could try something like this, but be careful

REGEX_Replace([Field1], ".*(?:\bLtd\b|\bOy\b|\bInc\b|\bPlc\b)\W*(.*)", "$1")

This probably might do the trick, if you see other words as patterns, you could include in the alternate expression.

Cheers,

sethuprc

Solution from @Thableaus is a good one

Another solution would be to use formula tool with

IF FindString([Field1],"Plc") >0 THEN
Trim(Right([Field1], Length([Field1]) - FindString([Field1],"Plc")-4))
ELSEIF FindString([Field1],"Oy") >0 THEN
Trim(Right([Field1], Length([Field1]) - FindString([Field1],"Oy")-3))
ELSEIF FindString([Field1],"Ltd") >0 THEN
Trim(Right([Field1], Length([Field1]) - FindString([Field1],"Ltd")-4))
ELSEIF FindString([Field1],"Inc") >0 THEN
Trim(Right([Field1], Length([Field1]) - FindString([Field1],"Inc")-4))
ELSE [Field1] ENDIF

adrrs

Thanks! This worked, although it gets ugly the more conditions you have.

tom_montpool

Using the Find Replace tool would make the workflow tidier because the number of 'conditions' is handled in the input to the tool, not in a nested formula.

tom_montpool

Here's an example of a workflow using Find Replace.

FindReplace.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703