Hi,

Basically I have lots of cells containing text such as:

BROOME WA
CENTENNIAL PARK NSW
CABLE BEACH WA
PORT MELBOURNE VIC

etc...

I want the state, E.g. the last "word" of the string, in another column. The state can be one of either WA, NT, NSW, ACT, VIC, QLD, SA, TAS.

One problem that occurs, however is that on the odd occasion, someone has not entered the data correctly and the state has been omitted.

This means every so often (500 cells or so) a cell contains text such as:

CABLE BEACH
BROOME

etc...

Is there a way that I can use a formula to check if the cell contains a state, if not, leave it. If it does contain a state, separate it from the rest of the cell. If there is a state it will always occur at the end of the string, however a string can be from 1 ~ 5 words.

Any help is greatly appreciated and Rep will obviously be given to anyone that helps solve the problem

Cheers,
--CameronP

---------- Post added at 12:07 PM ---------- Previous post was at 11:09 AM ----------

Anyone at all have an idea?

I need this for work and I am stumped, been trying to figure it out with LEFT, RIGHT and MID functions among others but I am really stuck.