How to split cell based on capitalised suburb name

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

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