Finding State Only

[stroffso]

Hi,

I have a list of addresses (Australian) in column B on my worksheet. I want to extract the state only. Note that some states have two characters and others have 3, what is the best formula for this?

Example of the address is as follows. For this I would just want to return "NSW" as the value

400 ELIZABETH STREET,SYDNEY,NSW 2000

<tbody>
</tbody>

thanks

 

[Teeroy]

Assuming that your addresses start at B2 try:

=TRIM(LEFT(RIGHT(SUBSTITUTE(B2,",",REPT(" ",99)),50),LEN(RIGHT(SUBSTITUTE(B2,",",REPT(" ",99)),50))-4))

and copy/fill down