Splitting strings based on values

Question

Hello

i have a workflow that manipulates data based on our employment system. When new staff join the firm they enter their home addresses, in different formats which I'd like to standardise with Alteryx Designer's help 😁

the different formats are :

U 3519-21 Smith  StreetNorth Kellyville Nsw 2145

1 Duck StreetRandwick Nsw 2031

A210/ 16-22 Swan RoadEpping NswEpping Nsw 2121

The layout I'd like is for

* the postcode ( e.g. 2145) in 1 column, Our postcodes are only 4 digits, with the first difit being 0,2,3,4,5,6 or 7 
* the State (e.g. NSW, ACT, QLD, SA, TAS, VIC, NT, QLD) in another column,
* the suburb (Epping, Randwick or North Kellyville) in another column
* the actual street address is currently running over 1 or 2 columns but i'd like to merge into 1 column if possible.

I'd be happy, at this stage to get the postcode and state in separate columns the if suburb and street address are too complicated.

Is this possible ?

Qiu · Accepted Answer

@StephenT 
Also tried for "actual street address" and "Surburb". 
But it is quite brutal, need more data to validate.

atcodedog05 · Accepted Answer

Hi @StephenT

Here is my take on it.

Workflow:

Hope this helps : )