Use Regex to Order Address

Question

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 Answer

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 Answer

Very similar to @MarqueeCrew from me:

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