Hi All,
I have the below addresses and like to transform them as in the Required Output column.
Address | Required Output |
1/1-3 Carboni Street | 11-3carboni St |
13/51-53 Cairds Avenue | 1351-53cairds Ave |
1/127 Humphries Road | 1127humphries Rd |
1 Badger Place | 1badger Pl |
10 Walpole Crescent | 10walpole Cres |
Regards,
Jag
Solved! Go to Solution.
In this case, I think we might not have enough of your possible data examples to craft a better solution than what MarqueeCrew wrote.
An alternative way to look at this problem is that there are three parts to each field: Numbers, Street name, Type. You could consider trimming and then tokenizing on the first and last spaces (not all spaces, in case we encounter a street name with a space in it). This way, when you find and replace on your street type (e.g. convert from Place to Pl) you would not capture a street name that includes the designated street types (e.g. you don't want "Jackson Place Boulevard" to turn into "Jackson Pl Blvd").
Once you've appropriately transformed each of the three fields, just recombine them using a formula such as: [Numbers] + [StreetName] + " " + [Type].
If I knew the regex you needed off the top of my head I'd provide it, but I usually pick up my more complicated regex expressions via a combination of StackOverflow and trial and error.