Split cell based on UPPERCASE text

  • Thread starter Thread starter Bentam3
  • Start date Start date
B

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
 
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.
 
Back
Top