Splitting address into designated columns

Masond3

HI All, 

I have the following problem and it has me stumped. 

I am being passed two columns (ID & Full Address)

ID	Full address
0001	Catalyst Inc\nBay Road\nLondonderry\nCounty Londonderry\nBT48 7TG\nBT 4 8 7 T G\nUNITED KINGDOM
0002	10 Thistle Street\nAberdeen\nAberdeenshire\nAB10 1XZ\nA B 1 0 1 X Z\nUNITED KINGDOM

However, I  would like to format the "full address" so its transposed into relevant columns

Expected Outcome :

ID	Full address	Street	City	State	Zip 1	Zip 2	Country
0001	Catalyst Inc\nBay Road\nLondonderry\nCounty Londonderry\nBT48 7TG\nBT 4 8 7 T G\nUNITED KINGDOM	Catalyst Inc Bay Road	Londonderry	County Londonderry	BT48 7TG	BT 4 8 7 T G	UNITED KINGDOM
0002	10 Thistle Street\nAberdeen\nAberdeenshire\nAB10 1XZ\nA B 1 0 1 X Z\nUNITED KINGDOM	10 Thistle Street	Aberdeen	Aberdeenshire	AB10 1XZ	A B 1 0 1 X Z	UNITED KINGDOM

Logic :

I have noticed that the Street can contain multiple lines, so to ensure that all the correct values are mapped to the correct columns, you need to start from the back of the full address(country) and work forwards 

Example 

Country -> Zip 2 -> Zip 1  -> State  -> City 

Then anything after the 5th extraction should be bundled into street 

Looking forward to your response 

TheOC

Hi @Masond3 
I found an interesting way of doing it - it may not necessarily be the best.

Step 1:

Start with the same dataset-

Step 2:
Reverse the string with a formula tool:

Step 3:
Split that column into 6 columns, ensuring to select 'leave extra in last column'

Ignore the fact the data looks AI generated at this point 😁

Step 4:
Reverse the strings again - using a multi-field formula as they are split:

This does the majority of what you needed - you can see that we have our data, and that any extra is contained in record 6.

Step 5:
Remove new line character in column '6', removing that is fairly easy with a data cleanse tool:

Step 7:

Use a select tool to rename and reorder:

And I believe we have it! You were totally right - we had to start from the beginning, so the way to do that was to reverse the string, and then follow the typical order.

I've attached the workflow - hopefully it should be fairly 'plug and play' with your data, but please let me know if you need any additional assistance. It follows the steps described above:

Kind Regards,
Owen

Masond3.yxmd