Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Split the contents of cells across multiple cells
How to Split the contents of cells across multiple cells
Hi , Im trying to split address from one cell across multiple cells with no success with Data / column to Text and then wizard if the suburb was only one word it would be no problem but sometimes it has two words Like Port Augusta then state and postcode example Greenwith WA 5122 works ok splits into 3 columns but Port Augusta WA 5147 not because I got Port August in one column and ta in next column and state and post code together and if there is a longer name it goes into for columns can you please help me to solve that Thank you in advance |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Split the contents of cells across multiple cells
Assuming that the state field is 3 characters and that the postcose is the
last four characters after one space For the city = Left( a1, len(a1)- 8) For the sate = left(right(a1,8),3) for the post code right(a1,4) "anna" wrote: How to Split the contents of cells across multiple cells Hi , Im trying to split address from one cell across multiple cells with no success with Data / column to Text and then wizard if the suburb was only one word it would be no problem but sometimes it has two words Like Port Augusta then state and postcode example Greenwith WA 5122 works ok splits into 3 columns but Port Augusta WA 5147 not because I got Port August in one column and ta in next column and state and post code together and if there is a longer name it goes into for columns can you please help me to solve that Thank you in advance |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Split the contents of cells across multiple cells
Hi Anna,
Is it only the 3 sets of data of suburb, state and postcode that you are splitting or do you also have other information like name address etc? If only suburb, state and postcode then what you could do is replace all the states with a leading and trailing colon and then use text to columns and set parameter to delimiters and use other and insert a colon as the delimiter. Instructions as follows:- Ensure that you have a backup of your data before you proceed with this in case you make an error. Select the column of data Select Replace In the Find what field, enter a space, the state abbreviation and a space In the Replace with field enter a colon, the state abbreviation and a colon Click Replace all. Repeat above for the remainder of the 8 states/territories. Select Text to columns Select Delimited option Click Next Select Other for the delimiter and then enter colon as the delimiter Select Next Select the column with the postcodes and then select Text (This is so that the postcodes for NT do not loose their leading zeros.) Click finish. -- Regards, OssieMac "anna" wrote: How to Split the contents of cells across multiple cells Hi , Im trying to split address from one cell across multiple cells with no success with Data / column to Text and then wizard if the suburb was only one word it would be no problem but sometimes it has two words Like Port Augusta then state and postcode example Greenwith WA 5122 works ok splits into 3 columns but Port Augusta WA 5147 not because I got Port August in one column and ta in next column and state and post code together and if there is a longer name it goes into for columns can you please help me to solve that Thank you in advance |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Split the contents of cells across multiple cells
On Wed, 28 May 2008 19:19:01 -0700, anna
wrote: How to Split the contents of cells across multiple cells Hi , I’m trying to split address from one cell across multiple cells with no success with Data / column to Text and then wizard if the suburb was only one word it would be no problem but sometimes it has two words Like Port Augusta then state and postcode example Greenwith WA 5122 works ok splits into 3 columns but Port Augusta WA 5147 not because I got Port August in one column and ta in next column and state and post code together and if there is a longer name it goes into for columns can you please help me to solve that Thank you in advance US terminology would be City State Zip Code Assuming that every address has a zip code -- and these should be either 5 digits or 9 digits in the US; and that every zip code is preceded by a two character state code (also standard in the US, then the following should work: A1: Address in above format City B1: =TRIM(SUBSTITUTE(A1,C1&" "&D1,"")) State C1: =TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE( A1," ",REPT(" ",13)),26)),D1,"")) Zip_Code D1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10)) If there is more variability, then a different solution may be necessary. --ron |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Split the contents of cells across multiple cells
Hi
OssieMac Thanks a lot it works I have more columns but I needed to extract State and postcode to filter my data Regards Anna "OssieMac" wrote: Hi Anna, Is it only the 3 sets of data of suburb, state and postcode that you are splitting or do you also have other information like name address etc? If only suburb, state and postcode then what you could do is replace all the states with a leading and trailing colon and then use text to columns and set parameter to delimiters and use other and insert a colon as the delimiter. Instructions as follows:- Ensure that you have a backup of your data before you proceed with this in case you make an error. Select the column of data Select Replace In the Find what field, enter a space, the state abbreviation and a space In the Replace with field enter a colon, the state abbreviation and a colon Click Replace all. Repeat above for the remainder of the 8 states/territories. Select Text to columns Select Delimited option Click Next Select Other for the delimiter and then enter colon as the delimiter Select Next Select the column with the postcodes and then select Text (This is so that the postcodes for NT do not loose their leading zeros.) Click finish. -- Regards, OssieMac "anna" wrote: How to Split the contents of cells across multiple cells Hi , Im trying to split address from one cell across multiple cells with no success with Data / column to Text and then wizard if the suburb was only one word it would be no problem but sometimes it has two words Like Port Augusta then state and postcode example Greenwith WA 5122 works ok splits into 3 columns but Port Augusta WA 5147 not because I got Port August in one column and ta in next column and state and post code together and if there is a longer name it goes into for columns can you please help me to solve that Thank you in advance |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to Split the contents of cells across multiple cells
Thanks Ron
"Ron Rosenfeld" wrote: On Wed, 28 May 2008 19:19:01 -0700, anna wrote: How to Split the contents of cells across multiple cells Hi , Im trying to split address from one cell across multiple cells with no success with Data / column to Text and then wizard if the suburb was only one word it would be no problem but sometimes it has two words Like Port Augusta then state and postcode example Greenwith WA 5122 works ok splits into 3 columns but Port Augusta WA 5147 not because I got Port August in one column and ta in next column and state and post code together and if there is a longer name it goes into for columns can you please help me to solve that Thank you in advance US terminology would be City State Zip Code Assuming that every address has a zip code -- and these should be either 5 digits or 9 digits in the US; and that every zip code is preceded by a two character state code (also standard in the US, then the following should work: A1: Address in above format City B1: =TRIM(SUBSTITUTE(A1,C1&" "&D1,"")) State C1: =TRIM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE( A1," ",REPT(" ",13)),26)),D1,"")) Zip_Code D1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10)) If there is more variability, then a different solution may be necessary. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split text to multiple cells | Excel Worksheet Functions | |||
How to split columns with multiple lines w/in cells | Excel Discussion (Misc queries) | |||
Excel 2000 - Split Contents of Cell Across Multiple Cells | Excel Discussion (Misc queries) | |||
Split non delimited data into multiple cells | Excel Worksheet Functions | |||
How to split the contents of a cell between two cells. | Excel Worksheet Functions |