Split cell based on UPPERCASE text

[Bentam3]

I'm trying to split some address data based on the suburb being in UPPERCASE.
Here's some sample data:

Level 21, 80 Collins Street, MELBOURNE
Level 2, East Wing, 2 Treasury Place, EAST MELBOURNE
57- 83 Kavanagh Street, SOUTHBANK

In the first column I'd just like the address (eg: Level 21, 80 Collins St)
and in the second column I'd the the suburb (eg: MELBOURNE).

The data is varying lengths with varying numbers of spaces and commas. The
one constant is that the suburb is always last and always in UPPERCASE.

Help please
Ben

 

[JBeaucaire]

A1: Text string
B1: =LEFT(A1, LEN(A1) - LEN(C1) - 2)
C1: =MID(A1, FIND("^", SUBSTITUTE(A1, ",", "^", LEN(A1) - LEN(SUBSTITUTE(A1,
",", "")))) + 2, 99)

The B1 formula won't return the correct answer until after you put in the C1
formula, so do them both. Then copy both cells down as far down as needed.