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]

@TheOC 

Thanks for the response, i tried this out however its not giving me the outcome as listed in your post ( see Attached image)

for some reason i am not able to export the full address, but this is part of the address 

Catalyst Inc\nBay Road\nLondonderry\nCounty Lo...

I assume the full path way would be like this 

Catalyst Inc\nBay Road\nLondonderry\nCounty Londonderry\nBT48 7TG\nB T 4 8 7 T G\nUNITED KINGDOM

as you can see everything is being dumped into the "Country" field 

[gaoa]

Hi @Masond3 

I tried another way to solve this (please see my workflow attached):

Let me know if it works or there are any questions. Appreciate your feedback. 😊

[TheOC]

Hey @Masond3 
I have split based on a new line character, rather than '\n', which of these occurs when you import your raw data?
Kind Regards,
Owen

[flying008]

Hi, @Masond3 

1- Another way for you : (there is only 2 rows data of you, could you please give 10 or more rows data for test ? )

Input
ID	Full address
1	Catalyst Inc\nBay Road\nLondonderry\nCounty Londonderry\nBT48 7TG\nBT 4 8 7 T G\nUNITED KINGDOM
2	10 Thistle Street\nAberdeen\nAberdeenshire\nAB10 1XZ\nA B 1 0 1 X Z\nUNITED KINGDOM
3	Street 1\nRoad 2\nOffice 3\nCity\nCityState\nZip 1\nZip 2\nCountry
Output
ID	Full address	Street	City	State	Zip 1	Zip 2	Country
1	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
2	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
3	Street 1\nRoad 2\nOffice 3\nCity\nCityState\nZip 1\nZip 2\nCountry	Street 1 Road 2 Office 3	City	CityState	Zip 1	Zip 2	Country

***

2- Regex to parse, so simple:

BTW, maybe you can provided about 10 rows sample data to test , this is be best !

[Masond3]

Hi @TheOC 

I can confirm that address is separated by a new line character. 

I looked at 80 records and 90% of the records looks good, however there is 10% where i have noticed some anomalies.

Looking at 80 examples, i can see that the  number of lines (line breaks) vary from  10 - 6

One pattern i can see is that where state = London, then City is blank

Consequently the "street" value  is then getting mapped into "city" and "street" is blank 

Example 

full_address	Street	City	State	Zip 1	Zip 2	2	Country
50 Liverpool Street London EC2M 7PD E C 2 M 7 P D UNITED KINGDOM		50 Liverpool Street	London	EC2M 7PD	E C 2 M 7 P D		UNITED KINGDOM
75 Prince Albert Road London NW8 7SA N W 8 7 S A UNITED KINGDOM		75 Prince Albert Road	London	NW8 7SA	N W 8 7 S A		UNITED KINGDOM
80 Coleman Street London EC2R 5BJ E C 2 R 5 B J UNITED KINGDOM		80 Coleman Street	London	EC2R 5BJ	E C 2 R 5 B J		UNITED KINGDOM

Expected out come 

full_address	Street	City	State	Zip 1	Zip 2	2	Country
50 Liverpool Street London EC2M 7PD E C 2 M 7 P D UNITED KINGDOM	50 Liverpool Street		London	EC2M 7PD	E C 2 M 7 P D		UNITED KINGDOM
75 Prince Albert Road London NW8 7SA N W 8 7 S A UNITED KINGDOM	75 Prince Albert Road		London	NW8 7SA	N W 8 7 S A		UNITED KINGDOM
80 Coleman Street London EC2R 5BJ E C 2 R 5 B J UNITED KINGDOM	80 Coleman Street		London	EC2R 5BJ	E C 2 R 5 B J		UNITED KINGDOM

If i assume that 10 is the max number of lines then 10-6 appears to be static ; 

Working backwards;

10 = Country

9 = Blank (There is a blank line between Zip 2 and country)

8 = zip 2

7 = zip 1

6 = State

5 

Example - 10 Line breaks :

Suite E
Canal Wharf
Eshton Road
Gargrave
Skipton
North Yorkshire
BD23 3AE
B D 2 3 3 A E

UNITED KINGDOM

Example - 6 Line breaks :

No.1 London Bridge
London
SE1 9BG
S E 1 9 B G

UNITED KINGDOM

Regards 
Masond3