Use Regex to Order Address

[jagjit_singh]

Hi,

I have got addresses in the below format: suburb, street address, state, country. I like to order them as street address, suburb, state, country. How can I use regex to achieve the correct format please.

Anna Bay 16-18 Margaret Street NSW Australia

Ballina 106 Cherry Street NSW Australia

Required Format

16-18 Margaret Street Anna Bay NSW  Australia

106 Cherry Street Ballina NSW Australia

Thanks

Jag

[MarqueeCrew · Accepted solution]

How about?

Regex_replace([address],"(.*)\s(\d.*)\s(\w.*)\s(\w{3})\s(.*)","$2 $1 $3 $4 $5")

i parsed it into 5 groups. 

[jdunkerley79 · Accepted solution]

Very similar to @MarqueeCrew from me:

REGEX_Replace([Address], "(.+)\s([\d\-]+) (.*) ([A-Z]{3}) (\w+)","$2 $3 $1 $4 $5")