Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to Sort and Address
I'm new to Excel and could use any help you are willing to share. I have a 300 page wortkbook, one column is address meaning street number and name and a simple sort will not sort them. Is there a way to sort so that I have all houses on the same street are together along with numbers in order as well?????
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Sort and Address
"krystal123" wrote:
I'm new to Excel and could use any help you are willing to share. I have a 300 page wortkbook, one column is address meaning street number and name [....] Is there a way to sort so that I have all houses on the same street are together along with numbers in order as well????? Assuming you have street number before street name, I think the easiest thing to do is create a couple helper columns. The alternative is to use VBA, which I would not advise for someone "new to Excel". I assume that you want street number in numerical order, e.g. 11, 12, 13, 111, 121, 131, etc. In that case, if your data are in A1:A300, put the following formula into B1 and C1, and copy B1:C1 down through B300:C300. B1: =--LEFT(A1,FIND(" ",A1)-1) C1: =MID(A1,FIND(" ",A1)+1,999) The use "--" before LEFT converts the numeric text into a number. The use of 999 with MID is arbitrary; just a big number to ensure that we get the entire length of the string. The select A1:C300 and use Sort, sorting first by column C, then by column B. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enable Excel to Sort by IP Address | Excel Worksheet Functions | |||
sort e-mail address | Excel Discussion (Misc queries) | |||
how do I sort email address by ISP? | Excel Worksheet Functions | |||
Sort IP address. | Excel Programming | |||
How Do I sort an IP address by the 3rd Octet? | Excel Worksheet Functions |