Parsing Address

[jatienza]

Hi!,

Need help in parsing the address into address 1, city, state, postcode, country. Below is the raw data and output.

Raw Data:

Address

Au. Ricchieri Km 17; Tapiales, Buenos Aires 1771, AR

Weerhuisweg 4; Maastricht, Limburg 6226, NL

Route de la Fènerie; Pégomas, Alpes-Maritimes 06580, FR

Lyndon B Johnson Fwy; Dallas, Texas 75237, US

Los Angeles , CA, US

Output:

address 1	city	state	postcode	country
Au. Ricchieri Km 17	Tapiales	Buenos Aires	1771	AR
Weerhuisweg 4	Maastricht	Limburg	06226	NL
Route de la Fènerie	Pégomas	Alpes-Maritimes	06580	FR
Lyndon B Johnson Fwy	Dallas	TX	75237	US
	Los Angeles	CA		US

[BS_THE_ANALYST · Accepted solution]

@jatienza Here's one way:

RegEx: 

[BS_THE_ANALYST · Accepted solution]

@jatienza  Actually this solution is better. It only uses one tool. I made use of an optional non-capturing group:

[jatienza]

Hi @BS_THE_ANALYST , it works but does not work if some address are unstructured.

[BS_THE_ANALYST]

@jatienza can you provide me with some examples of where it's not working? Also the expected output too, it'll be quicker for me to build the logic

[jatienza]

@BS_THE_ANALYST below is a sample data. I added address 2 field. Should also apply on the first raw data I posted.

Address

ATM conNorte; Mza. Tt. 9; Playa del Carmen, Quintana Roo 77710, MX

Actor Dr; Woodstock, Ontario N4V 1B9, CA

105 Monnet; Elancourt, FR

Valencia, ES

4th St; Davenport, Iowa 52801-1902, US

Output:

address 1	adress 2	city	state	postcode	country
ATM conNorte	Mza. Tt. 9	Playa del Carmen	Quintana Roo	77710	MX
Actor Dr		Woodstock	Ontario	N4V 1B9	CA
105 Monnet			Elancourt		FR
		Valencia			ES
4th St		Davenport	IA	52801-1902	US

[BS_THE_ANALYST]

@jatienza this is where things become really difficult. Look at that particular pattern:

They both match exactly the same but you want them in different columns. Also, some addresses now appear to have Address 1, Address 2? There could be Address 3, Address .. N.

When this amount of variety comes in, it's hard to establish a catch all RegEx expression. 

I saw a recent post here trying to accomplish similar things: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Parsing-Out-City-Name/m-p/1083755/high... . Potentially you could use a lookup table to parse parts out using this concept.