How to sort addresses by street name/number in Excel?

Let's say you have a list of addresses of your clients in a worksheet, and you want to sort these addresses by their street name. So how can you quickly sort your clients' addresses by street name/number in Excel?

Sort addresses by street name with a help column in Excel

Sort addresses by street number with a help column in Excel

Sort addresses by street name with a help column in Excel

Sort addresses by street name in Excel, you need to create a help column first.

1. Select a blank cell adjacent to the address list, and type this formula =MID(A1,FIND(" ",A1)+1,255) (A1 is the cell of your address list), and press Enter button, then drag the fill handle to fill the range you want. After that, you can see all addresses are extracted to the help column except the street numbers.

2. Then select the help column range, and click Data > Sort A to Z. See screenshot:

3. In the Sort Waning dialog box, check Expand the selection. See screenshot:

4. Click Sort, you can see the addresses are sorted.

Sort addresses by street number with a help column in Excel

If the addresses are with the same street, and you want to sort them by the street number, you can do as these:

1. Select a blank cell which is adjacent to the address list, and type this formula =VALUE(LEFT(A1,FIND(" ",A1)-1)) (A1 is the first cell of your address list), and press Enter button, then drag the fill handle to fill the range you want. After that, you can see only the street numbers are extracted to the help column.

2. Then select the help column range, and click Data > Sort A to Z. See screenshot:

3. In the Sort Waning dialog box, check Expand the selection. See screenshot:

4. Click Sort, you can see the addresses are sorted.

Tip: You also can use this formula =LEFT(A1,FIND( " ",A1)-1), too.

Note: This method also can work when the streets in the addresses are different.

Relative Articles:

• Sort email addresses by domain in Excel
• Sort data by font color in Excel