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

[Rana_Kareem]

Hi @BMartinCOE ..

You can use Find Replace Tool.

See the attachment..

[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 · Accepted solution]

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. 

[Rana_Kareem]

@BMartinCOE 

My solution was right!

I added the same values and got the desired result!!

You may have forgotten to select (Match Whole Word Only)

You can find more about this tool here