Dynamically Replace String Ends

BMartinCOE

Hello,

I am working to clean up an address list that currently looks like this

1234 ST

456 STREET

12 AVE

5555 AVENUE

I would like to get it in the format:

1234 STREET

456 STREET

12 AVENUE

5555 AVENUE

I tried using a formula like:

IF Contains(RIGHT([Building Address], 3), "AVE") THEN "AVENUE" but this does not replace the wording just create a new column that says AVENUE.

I tried to add replace to the above formula but get a parse error.

Any thoughts? THan

Workflow

Help

Accepted answers

BMartinCOE

I decide to use the text to column tool splitting based on space and then i had 3 different columns such as:

field 1 field 2 field 3

123 Maple ST

And this allowed me to easily change field 3 and then concatenate them all back together.

All comments

Rana_Kareem

Hi @BMartinCOE ..

You can use Find Replace Tool.

See the attachment..

Find Replace Tool.yxmd

BMartinCOE

Rana, thank you for your time and effort. This solution is much more simple than what i had tried, much appreciated!:)

Rana_Kareem

Glad to be of help

BMartinCOE

Hi Rana,

Turns out this does not work as the solution because for example the data looks like this:

1234 STUMP ST

123 MAVE AVE

123 MAPLE AVENUE

Using your solution, it would output

1234 STREETUMP STREET

123 MAVENUE AVENUE

123 MAPLE AVENUE

I tried to solve for this by putting a space in front of the abr field but that still will cause some issues. Any thoughts? Thinking I might have to return to something similar to my first attempt.

BMartinCOE

I decide to use the text to column tool splitting based on space and then i had 3 different columns such as:

field 1 field 2 field 3

123 Maple ST

And this allowed me to easily change field 3 and then concatenate them all back together.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703