Regex Formula to extract unorganised address

[Myusrename001]

Hi Team,

I would needed your help on building a Regex formula that breaks the unorganised address as per below specificaiton.

1)  Pick the postal code - (it could be 4,5, 6, 7 digit alpha numeric) after 2 digit country code:

2) Process 2 digit country code as it is 

3)  Create city any 2 words before country code

4) Anything else should be marked as "Address line"

Here is my raw data;

Kolpingstrasse 48C       Schloss Holte-Stukenbrock   DE 33758

Ottenheide 35       Schloss Holte- Stukenbrock   DE 33758

34/29, Flat 3A 3rd Floor, 2nd Main road kasturibai nagar, Adyar     chennai Tamil nadu IN 600020

704A La Hacienda 31-33 Mt Kellett Road     Peak Islands HK

Glueck-Auf-Strasse 24       Wilnsdorf   DE 57234

3308, Beverly Road Highland Park     Dallas Texas US 75205-2927

can any one help me - thanks in advacnce.

Regards
 

[ChrisTX]

If you develop a RegEx that works for the current data, it may be difficult to maintain in the future.  Try breaking the problem up into smaller steps, which will likely be easier to maintain.

The attached workflow should give you a starting point.

 

Chris

[Myusrename001 · Accepted solution]

@ChrisTX  Thanks so much for your solutiona and its working perfectly.