extract text from single field using leftwords-rightwords-wordcount
Currently i am trying to extract the State (always second word from the right) from an address field with only spaces in it. Eg: Allambie Heights NSW 2100 not all suburbs have 2 names Eg: Abbotsford NSW 2046.
i have been able to extract the suburb using: (LeftWords ( Contact Management::Correspondence Suburb ; WordCount ( Contact Management::Correspondence Suburb ) - 2 ) ).
As well at the poste code using: RightWords ( Contact Management::Correspondence Suburb ; 1 )
But am stuck on the State, some have 3 intinals and others 2. The state is always positioned as above with no commas.
Help would be appreciated.