If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|