How to split cell based on capitalised suburb name
 

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

How to split cell based on capitalised suburb name
 

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
---------------
Jacob Skaria


"Bentam3" wrote:

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

How to split cell based on capitalised suburb name
 

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Bentam3" wrote:

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

How to split cell based on capitalised suburb name
 

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
---------------
Jacob Skaria


"Bentam3" wrote:

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Bentam3" wrote:

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