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

Parse state code from string

StephenJPSheehan
7 - Meteor

Hello,

 

I have a range of addresses for different countries contained within a single column of data, which is separated by a space. Note that the detail of address varies by row, so some may, for example, include Street City Postcode and State whereas others may contain only City and State. The only commonality is that all the state codes are capital letters only and are either 2 or 3 characters.

 

I've been trying to get a Regex Parse to work in extracting the data by trying to code it to recognise multiple capital letters next to each other in a string (other parts of the address have a single capital letter for example in the street or city), but am having difficulties in getting this to work. Does anyone have any advice in how this could work using Regex or a different function?

 

Appreciate any advice in advance!

5 REPLIES 5
messi007
15 - Aurora
15 - Aurora

@StephenJPSheehan,

 

Could you share a sample data? It will help us to check with you.

 

Regards,

StephenJPSheehan
7 - Meteor

Hello,

 

Please see below for some examples:

 

English Creek Ave Egg Harbor Township NJ 08234

QLD 4875, Australia

Bohle QLD 4818 and Innisfail, QLD 4860

 

As you can see there isn't much consistency in terms of order, the only consistency is the 2-3 capital letter characters showing the State code.

 

Appreciate your thoughts.

cgoodman3
14 - Magnetar
14 - Magnetar

([A-Z]{2,3})

that will give you all uppercase letters of length between 2-3 characters in length.

 

Do you have a spatial licence as there is an address tool where you can send in a partial address as a single field and get out additional data for street name, city, state, zip code etc if these are missing.

Chris
Check out my collaboration with fellow ACE Joshua Burkhow at AlterTricks.com
apathetichell
18 - Pollux

With regex parse it's worth double checking to make sure that you have the "case insensitive" check box unchecked... assuming it is - @cgoodman3's strategy should work. You can also use \u in place of [A-Z].

StephenJPSheehan
7 - Meteor

Thanks Chris - your solution worked perfectly. Also thanks to @apathetichell too - for some reason when I was testing various RegEx expressions I somehow missed the checkbox, which may have also explained my frustration in trying to solve the problem!

Labels