 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2004sorting residential addresses
I have a list of about 100 addresses. I have them in Excel. I can sort the list by name, or phone number but when I try to sort the addresses it comes out with the numerical entry first. Here is and example: 11552 Green Dr 11555 Brown Dr 11556 Green Dr 11557 Blue Dr But I want it to look like this: 11557 Blue Dr 11555 Brown Dr 11552 Green Dr 11556 Green Dr In other words I want it to sort by the Name of the Stree First and then the lower numerical address number. Is there any way to do this? Thanks. AFAIK you need to break down to 123 street with data>text to columns or use a dummy column =MID(A1,FIND(" ",A1,1),LEN(A1))  SignatureDon Guillett SalesAid Software donaldb@281.com > I have a list of about 100 addresses. I have them in > Excel. I can sort the list by name, or phone number but [quoted text clipped - 12 lines] > First and then the lower numerical address number. > Is there any way to do this? Thanks. Hi Tim, Text to columns is going to break up 10001 west apple orchard way into 5 columns You can use a macro for what I think is the best, if you want a permanent change. solution http://www.mvps.org/dmcritchie/excel/join.htm#septerm Without a macro you can add two columns, use a worksheet solution and keep the formulas or delete them afterwards. =LEFT(A3,FIND(" ",A3)-1) =MID(A3,FIND(" ",A3)+1,LEN(A3)-FIND(" ",A3)) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > AFAIK you need to break down to 123 street with data>text to columns or use > a dummy column [quoted text clipped - 17 lines] > > First and then the lower numerical address number. > > Is there any way to do this? Thanks. > I have a list of about 100 addresses. I have them in > Excel. I can sort the list by name, or phone number but [quoted text clipped - 12 lines] > First and then the lower numerical address number. > Is there any way to do this? Thanks. Assuming your data is in Columns A:C, Select the data, click on Data|Sort|Column B Ascending| Column A Ascending Alan Beban If the data shown in your example is all in one cell, you could break the addresses into two columns and sort on the street names. Data>Text to Columns could break them into two columns or you could use a helper column and enter a formula such as...... =MID(A1,FIND(" ",A1)+1,LEN(A1)) Will put Green Dr in the helper cell. Gord Dibben Excel MVP >> I have a list of about 100 addresses. I have them in >> Excel. I can sort the list by name, or phone number but [quoted text clipped - 17 lines] > >Alan Beban are these in one column. in that case data-texttocolumns and use delimer as <spacae>. the data will go into three coumns. then you can sort one by one column first <street>ascending and then by no. coumn > I have a list of about 100 addresses. I have them in > Excel. I can sort the list by name, or phone number but [quoted text clipped - 12 lines] > First and then the lower numerical address number. > Is there any way to do this? Thanks. SignatureUsing Opera's revolutionary e-mail client: http://www.opera.com/m2/ US Street address are not limited to a number a streeetname and type of street so text to columns will not work. Valid example data (test data) that shows different cases is important when asking a question. But it is up to the user to implement a correct solution, we can only guess and suggest. i.e. 900 Boulevard 41 Washington Ave 104 Old Stage Rd 1024 Route 30 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > are these in one column. in that case data-texttocolumns and use delimer > as <spacae>. the data will go into three coumns. [quoted text clipped - 17 lines] > > First and then the lower numerical address number. > > Is there any way to do this? Thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.