How to sort addresses by street name/number in Excel?
When you manage a list of addresses in Excel, it is often necessary to organize or analyze the data by sorting addresses either by street name or by street number. For example, if you need to group clients living on the same street together, or you must process deliveries in order of house numbers, sorting by these components is essential. However, since typical address formats mix street names and numbers within a single cell, straightforward sorting won't give the expected results. In this article, we'll discuss practical methods to sort addresses by street name or street number in Excel, analyze their benefits and application scenarios, and offer troubleshooting and alternative solutions for various user needs.
Sort addresses by street name with a help column in Excel
Sort addresses by street number with a help column in Excel
Sort addresses using VBA to extract and sort by street name or number automatically
Sort addresses by street name or number with Power Query (no helper columns)
Sort addresses by street name with a help column in Excel
To sort addresses by street name in Excel, you'll first need to extract just the street names into a helper column. This approach is straightforward and works well when the address format is consistent, such as "123 Apple St". It's suitable for quick projects or simple address lists.
1. Select an empty column adjacent to your list of addresses. Enter the following formula in the first cell of the helper column to extract the street name:
=MID(A1,FIND(" ",A1)+1,255)(Here, A1 refers to the top cell of your address data—adjust if your data starts elsewhere.)
After typing the formula, press Enter and then drag the fill handle down to apply the formula for all rows in your address range. This formula works by finding the first space in each address, then returning everything after that space—the street name and any suffix. Make sure your addresses follow the same structure; otherwise, the formula may not split as expected.
2. Highlight the entire helper column (the column with extracted street names), then go to the Data tab and click Sort A to Z. This will sort the street names in ascending (alphabetical) order.
3. In the Sort Warning dialog box that appears, select Expand the selection to ensure that the full address information stays together when sorting.
4. Click Sort. Your address list will now be re-ordered based on street names, making similar streets appear together.
Note: This method works best with standardized address formats. If your address cells contain irregular patterns or multiple spaces before the street name, the formula may need to be adjusted. Always check a few results for accuracy after using the formula.
Pros: Simple, does not require additional tools.
Cons: Relies on consistent formatting; extra work required if address format varies.
Sort addresses by street number with a help column in Excel
If you need to sort a list of addresses by the numerical street number—such as for assigning a delivery order, or identifying neighboring addresses—it's easy to extract the number and use it for sorting. This is also effective even when the addresses are on different streets.
1. In a blank cell next to your address list, enter the following formula to pull out the street number:
=VALUE(LEFT(A1,FIND(" ",A1)-1))(Where A1 is the first address in your list—adjust as needed.) Press Enter after typing it. This formula works by locating the first space and returning the characters before it, converting them to a numeric value. If your addresses have leading digits as street numbers, this formula will work correctly. Then, drag the fill handle down to apply the formula to the rest of your list.
2. Select the helper column you just created, go to the Data tab, and click Sort A to Z (or Sort Smallest to Largest for newer Excel versions).
3. In the Sort Warning dialog, choose Expand the selection to sort full rows.
4. Click Sort to apply. Your addresses are now sorted by the extracted street number.
Tip: If you prefer to keep the street number as text, or you do not need to perform numeric sorting, you may also use:
=LEFT(A1,FIND(" ",A1)-1)This version will extract the number as a text string.
Precautions: If addresses start with words instead of numbers (such as "Main Street5"), these formulas will not work as intended. Double-check your address data before using the formula.
Pros: Quick and user-friendly if the address format is simple.
Cons: Does not handle addresses with names/suffixes preceding the number, nor addresses with multiple numbers.
VBA Code - Automate sorting addresses by extracting street names/numbers and sorting the list with a macro
For those working with larger, more complex address lists, or whose data includes variable address structures, automating the sorting process using VBA can be highly effective. VBA allows you to quickly extract street names or numbers, sort your address list automatically, and minimize manual steps. This solution is suitable when you periodically need to perform sorting, or want to integrate sorting into a workflow.
Note: This VBA macro extracts the street name (the part after the first space) from each address in column A and sorts the entire list based on these names. It also works for extracting and sorting by street number with minor adjustments.
1. Click Developer tab > Visual Basic. In the window that appears, click Insert > Module, and paste the following VBA code into the module window:
Sub SortAddressesByStreetName()
Dim ws As Worksheet
Dim lastRow As Long
Dim tempCol As Long
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
tempCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
' Create helper column with street names
For i = 1 To lastRow
ws.Cells(i, tempCol).Value = Trim(Mid(ws.Cells(i, 1).Value, InStr(ws.Cells(i, 1).Value, " ") + 1))
Next i
' Sort the whole data range by the helper column
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=ws.Range(ws.Cells(1, tempCol), ws.Cells(lastRow, tempCol)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, tempCol))
.Header = xlNo
.Apply
End With
' Delete helper column
ws.Columns(tempCol).Delete
End Sub2. To run the code, with the address list active, click the 
button or press F5. Your address list in column A will now be sorted alphabetically by street name.
This version extracts just the number before the first space and sorts by numerical order.
Troubleshooting:
- Confirm the addresses are in column A or update the code for your data's location.
- If your data includes a header, you may need to adjust Header = xlYes to avoid sorting the header row.
- Always create a backup before running bulk VBA code.
Pros: No helper columns required; works for large datasets or repetitive sorting.
Cons: Initial setup requires macro permissions and basic understanding of VBA.
Other Built-in Excel Methods - Use Power Query to split address columns and sort directly within Power Query without helper columns
Power Query, available in modern Excel versions (Excel 2016 and later, as well as Microsoft 365), provides a flexible, no-formula way to split addresses into components such as street number and street name. This solution is ideal if you prefer to avoid formulas and helper columns, or if your addresses follow varying formats that basic formulas can't handle efficiently. Power Query can also save your steps so you can update them as your data grows.
1. Select your address data and go to the Data tab, then choose From Table/Range (create a table if prompted).
2. In the Power Query window, select your address column, then click Split Column > By Delimiter. Choose Space as the delimiter, and select the first left-most delimiter for the Split at type.
3. This will split the address into two columns: the street number and the remaining street name/address. Rename the new columns as necessary.
4. To sort, click the arrow in the column header of either the street name or street number column and select Sort Ascending or Sort Descending.
5. Click Close & Load to insert the sorted results back into your worksheet.
Extra Tips:
- If your address pattern is not consistent, you can further manipulate columns in Power Query using custom splits or transformations.
- Power Query steps are automatically recorded; you can refresh the data easily if your source changes.
- This method does not alter your original data, enhancing safety for original records.
Pros: No permanent alteration of your sheet; robust for complex address patterns; no formulas to manage.
Cons: Requires Excel 2016 or newer; interface may be unfamiliar for new users.
Summary and troubleshooting suggestions:
- Remember to check the consistency of your address format before applying formulas or VBA.
- Always preview sorting results to confirm correctness, especially after using helper columns or code.
- For data with unexpected structure (such as missing numbers or street names at the end), adjust formulas or consider Power Query for more robust splitting.
- Make regular backups before using VBA or advanced data tools to avoid accidental data loss.
- Choose a solution (formulas, VBA, Power Query) that aligns best with your data volume, Excel version, and your comfort level with the tool.
- If you’re not sure which method is best, Power Query often offers the most flexibility and is safest for non-destructive editing.
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in