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
|
|||
|
|||
Splitting Cell Data?
OK here's a great one. Check this out. I have a simple one page spreadsheet of addresses. My client wants me to sort/filter the data so that it is sorted by street name first, then by address number. The problem is that the spreadsheet has 30,000 entries and the Address Column contains the street number and street name (i.e. 12345 Park Drive Blvd. Apt #345). So my question is: does anyone know how I can separate the street number portion of the address from the street name portion of the address to be put into two separate columns so I can sort them?
Thanks in advance, Dax |
#2
|
|||
|
|||
Splitting Cell Data?
Hi
if the street number is always at the beginning of your data use a helper column to extract the streetname and enter the following formula =MID(A1,FIND(" ",A1)+1,255) and copy this down for all rows -- Regards Frank Kabel Frankfurt, Germany Dax Arroway wrote: OK here's a great one. Check this out. I have a simple one page spreadsheet of addresses. My client wants me to sort/filter the data so that it is sorted by street name first, then by address number. The problem is that the spreadsheet has 30,000 entries and the Address Column contains the street number and street name (i.e. 12345 Park Drive Blvd. Apt #345). So my question is: does anyone know how I can separate the street number portion of the address from the street name portion of the address to be put into two separate columns so I can sort them? Thanks in advance, Dax |
#3
|
|||
|
|||
Splitting Cell Data?
You could try using "TextToColumns".
Make note of the column letter at the end of the data list. Select the address column, then: Data TextToColumns Next, Click in "Space", then Next. Now, count the number of columns that are displaying the separations. Make sure that you have that many *empty* contiguous columns at the end of the data list. In the "Destination" window, enter the empty column at the end of the list, Then Finish. You now have your addresses broken up into several columns. You can now choose any column you wish as sort key 1, and any other as sort key 2 ... and so on. When your finished sorting, simply delete these "helper" columns if you wish. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dax Arroway" Dax wrote in message ... OK here's a great one. Check this out. I have a simple one page spreadsheet of addresses. My client wants me to sort/filter the data so that it is sorted by street name first, then by address number. The problem is that the spreadsheet has 30,000 entries and the Address Column contains the street number and street name (i.e. 12345 Park Drive Blvd. Apt #345). So my question is: does anyone know how I can separate the street number portion of the address from the street name portion of the address to be put into two separate columns so I can sort them? Thanks in advance, Dax |
Thread Tools | |
Display Modes | |
|
|