extract text from single field using leftwords-rightwords-wordcount

[tmoreland]

Hi guys,

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.

[comment]

always second word from the right

Try =

MiddleWords ( text ; WordCount ( text ) - 1 ; 1 )

[tmoreland]

Try =

MiddleWords ( text ; WordCount ( text ) - 1 ; 1 )

Legend, been staring at this for hours, it just became a blur.

Thank you