Dynamically Replace String Ends
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Rana, thank you for your time and effort. This solution is much more simple than what i had tried, much appreciated!:)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Glad to be of help :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
 
