Hi everyone I am sorry if this question has been asked already but I'm new here and would like to find an answer quickly as I am very frustrated.
I work with a large amount of addresses and when I try to sort them they don't sort in numerical order. The addresses I pasted below are not how I expected them to be. I expected that 19 River Lane would come before 1015 Barton Ave. What am I doing wrong. One last bit of info is that I am exporting the addresses from another program.
1 WALLIS CT
1015 BARTON AVE
1112 BAY AVE
1141 HOLLYWOOD BLVD
120 POLK DR
128 PRIMROSE LN
19 RIVER LN
2612 NEWARK AVE
385 S LAKE DR
454 EAGLE PASS
522 BRICK BLVD
571 PARKER AVE
917 BORDEN AVE
The addresses are sorted alphabetically. If you just want to sort on the street number, add a column with a formula like =VALUE(LEFT(B1,FIND(" ",B1,1))), where the street address is in column B. Do a custom sort on columns A & B, with A defining the sort order. Sorted, the addresses look like this:
1 WALLIS CT
19 RIVER LN
120 POLK DR
128 PRIMROSE LN
385 S LAKE DR
454 EAGLE PASS
522 BRICK BLVD
571 PARKER AVE
917 BORDEN AVE
1015 BARTON AVE
1112 BAY AVE
1141 HOLLYWOOD BLVD
2612 NEWARK AVE
Last edited by geoB; 09-18-2011 at 05:10 PM. Reason: cant' even spell my own name!
My intention is to use text to column to make individual columns for the Street # & Street Name. If I do the =left etc. I have to hide the column with the original data in it. Don't need the original data once they're separated. My thinking was if I can sort them with all the single digit, then double digit, etc. addresses would be aligned and I could easily just use the text to columns and it would be smooth.
Thank you for your prompt reply
It's not clear what your end result should look like. Perhaps you could provide an example of a result that includes street numbers on the same street. Are these grouped such that all the Main St.s are in numerical order, followed by all the Pine St.s in numerical order, etc? Or just the single digit addresses, followed by two digits, etc.?
g
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks