Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Remove space and Characters

jagjit_singh
8 - Asteroid

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 Street11-3carboni St
13/51-53 Cairds Avenue1351-53cairds Ave
1/127 Humphries Road1127humphries Rd
1 Badger Place1badger Pl
10 Walpole Crescent10walpole Cres

 

 

Regards,

Jag

2 REPLIES 2
MarqueeCrew
20 - Arcturus
20 - Arcturus
Regex_replace(address,"\d\s",'\d')

That might work for you. But you will need to create a list of types and their replacements. Use a find and replace to change PLACE with PL as an example.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
david_fetters
11 - Bolide

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.

Labels