How to split cell based on capitalised suburb name

[Bentam3]

Hi

I have data such as the following in a single cell per line

Level 8, 160 Marsden St, PARRAMATTA
15 Carter Street, HOMEBUSH BAY
223 - 239 Liverpool Road, ASHFIELD

I want to put the Suburb name into a seperate cell without going through
hundreds of rows of data manually. There can be spaces in the suburb name
and multiple commas in the field. The only way to differentiate the suburb
name is that it is in UPPERCASE. Does anyone know a formula or macro that
could complete this?

Thanks

 

[Jacob Skaria]

Check out whether the below would help. The below will extract the
information after the last comma.

=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",255),LEN(A1)-
LEN(SUBSTITUTE(A1,",",))),255))

If this post helps click Yes

 

[Bentam3]

Perfect. Thank you. Now I just go back and break down why it works

 

[Jacob Skaria]

Here we are substituting the last instance of comma with 255 spaces and then
getting the trimmed string from the right.

To find the number of commas in the text string we have used
=LEN(A1)-LEN(SUBSTITUTE(A1,",",))

If this post helps click Yes