Convert one column of Addresses into three columns when text to be grouped is not even

Hi All,

I have checked to see if this has been answered before and can not find an exact reply.
My problem is that I have a combined column of address: Suburb, State, Postcode - which needs to be broken into three which not be done accurately using tet to column or fx - left mid or right.
The issue is that the Suburb could be made up of one or two Words with a space between. So I can not separate using text to columns as in some cases the suburbs comprised of two words will put the second word in the "state" column. It can also not be done using Left, Right,Mid, as they number of characters differs in each line. Hope someone can help and apologies if its been explained elsewhere. Thanks!
example problem with columns to text.jpg

COMBINED ADDRESS SUBURB STATE?? POSTCODE?? POSTCODE??
ALTONA NORTH VIC 3025 ALTONA NORTH VIC 3025
BONDI NSW 2026 BONDI NSW 2026
WOOLLOONGABBA QLD 4102 WOOLLOONGABBA QLD 4102
TOOWONG QLD 4066 TOOWONG QLD 4066
NOVAR GARDENS SA 5040 NOVAR GARDENS SA 5040

Hi Hemesh,
I have attached the end desired end result. thank you for your reply!example problem with columns to text.jpg

@ hemesh - the second formula that you suggested for cell C2 will give the results: NORTH, NSW, QLD etc (working down) but that is not the desired result. The places with the 2 first names e.g. Novar Gardens and Altona North are the problems here,

@ oddypatrick - I have tried to create what you need but I am sure there are folks here much better than I who will give you better results. Anyway look at the attached.

TEXT - STATES.xlsx

Hi Hemesh,

Thank you for sending through some macros.. they did not quite work however its very close.
Attached is the excel spreadsheet showing the results of the first Fx you posted.
I've just seen you next post, will that solve the problem?
thanksExample address column separated to 3 columns.xlsx

I think you might have accidently sent back my original file not your improved one..

Hemesh & Ursal,
That is Perfect! many thanks, I have spent 4 hours today reading, watching youtube and trying to work it out myself..you have both helped me enormously in half an hour.

This was my first time ever! using a forum.. and I wish I had joined 10 years ago.


Just Fantastic!

Form one aussie to another - you're welcome mate! Cheers