Assuming that the state field is 3 characters and that the postcose is the
last four characters after one space

For the city = Left( a1, len(a1)- 8)
For the sate = left(right(a1,8),3)
for the post code right(a1,4)

Hi Anna,

Is it only the 3 sets of data of suburb, state and postcode that you are
splitting or do you also have other information like name address etc?

If only suburb, state and postcode then what you could do is replace all the
states with a leading and trailing colon and then use text to columns and set
parameter to delimiters and use other and insert a colon as the delimiter.

Instructions as follows:-

Ensure that you have a backup of your data before you proceed with this in
case you make an error.

Select the column of data
Select Replace
In the Find what field, enter a space, the state abbreviation and a space
In the Replace with field enter a colon, the state abbreviation and a colon
Click Replace all.

Repeat above for the remainder of the 8 states/territories.

Select Text to columns
Select Delimited option
Click Next
Select Other for the delimiter and then enter colon as the delimiter
Select Next
Select the column with the postcodes and then select Text (This is so that
the postcodes for NT do not loose their leading zeros.)
Click finish.

US terminology would be  City State   Zip Code

Assuming that every address has a zip code -- and these should be either 5
digits or 9 digits in the US; and that every zip code is preceded by a two
character state code (also standard in the US, then the following should work:

A1:    Address in above format

City
B1:    =TRIM(SUBSTITUTE(A1,C1&" "&D1,""))

State
C1:
   
=TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(
A1," ",REPT(" ",13)),26)),D1,""))

Zip_Code
D1:    =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))

If there is more variability, then a different solution may be necessary.
--ron