ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   how to sort addresses in microsoft excel (https://www.excelbanter.com/excel-worksheet-functions/195360-how-sort-addresses-microsoft-excel.html)

GAW

how to sort addresses in microsoft excel
 
I have 2 lists of addresses on one sheet. Most of the addresses are
duplicates and I want to sort them so it puts all the duplicates together.
The streets include the house number, as 1257 Rock St. I can sort them by
street alone but I need to sort by street and the address together, is there
a way to do this? I am using Excel 2000, 9.0.2720.

recrit

how to sort addresses in microsoft excel
 
On Jul 17, 5:43*pm, GAW wrote:
I have 2 lists of addresses on one sheet. Most of the addresses are
duplicates and I want to sort them so it puts all the duplicates together..
The streets include the house number, as 1257 Rock St. I can sort them by
street alone but I need to sort by street and the address together, is there
a way to do this? I am using Excel 2000, * 9.0.2720.


if your address is always in the format of 1257 Rock. St, you can
split it into 2 columns, 1 for the number, 1 for the street
number =MID(I25, 1, SEARCH(" ", I25, 1)-1)
street =MID(I25, SEARCH(" ", I25, 1)+1, LEN(I25))

then use excel to sort by street column first, then by number column

recrit

how to sort addresses in microsoft excel
 
On Jul 17, 11:00*pm, recrit wrote:
On Jul 17, 5:43*pm, GAW wrote:

I have 2 lists of addresses on one sheet. Most of the addresses are
duplicates and I want to sort them so it puts all the duplicates together.
The streets include the house number, as 1257 Rock St. I can sort them by
street alone but I need to sort by street and the address together, is there
a way to do this? I am using Excel 2000, * 9.0.2720.


if your address is always in the format of 1257 Rock. St, you can
split it into 2 columns, 1 for the number, 1 for the street
number *=MID(I25, 1, SEARCH(" ", I25, 1)-1)
street * *=MID(I25, SEARCH(" ", I25, 1)+1, LEN(I25))

then use excel to sort by street column first, then by number column


to clarify, i25 was the cell containing the address in my test case


All times are GMT +1. The time now is 08:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com