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

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.
--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Bentam3" wrote:

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