Need help in parsing address

[jatienza]

Hi,

Need help in parsing the below raw data. Also, output is shown below.

Raw Data

Address

Baden-Württemberg 86797

Vendée 87000

Нижнесилезское воеводство  51-501

Hauts-de-France 86740

Île-de-France 75188

Distrito Federal 72905-450

Woj. Mazowieckie 02-952

São Paulo

Bogotá D.C 137211

Output

add1	add2
Baden-Württemberg	86797
Vendée	87000
Нижнесилезское воеводство	51-501
Hauts-de-France	86740
Île-de-France	75188
Distrito Federal	72905-450
Woj. Mazowieckie	02-952
São Paulo
Bogotá D.C	137211

[apathetichell]

Sao Paulo was being annoying - let me fix that.

add1:

regex_replace([Address],"^(.*)\s(\d+-*\d*)$","$1",1)

ugly add2:

regex_replace(replace([Address],[add1],""),"^\s*(\d+-*\d*)$","$1",0)

[martinding · Accepted solution]

Hi @jatienza 

You could try this:

[apathetichell]

@martinding 's is superior - run with that answer unless you have a paranoid aversion to the regex tool.

[binu_acs · Accepted solution]

@jatienza Another way of doing this 

[jatienza]

Thank you for you help! @binu_acs @apathetichell @martinding