Split cell based on UPPERCASE text
 

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

Split cell based on UPPERCASE text
 

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