KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中按街道名称或编号对地址进行排序？

作者孙修改日期

在 Excel 中管理地址列表时，通常需要按街道名称或门牌号对地址进行排序，以便高效组织和分析数据。例如，当您需要将同一街道的客户归为一组，或按门牌号顺序安排配送任务时，按这些关键字段排序就显得尤为重要。然而，由于标准地址格式通常将街道名称与编号合并存储在同一单元格中，直接排序往往无法获得理想结果。本文将为您介绍在 Excel 中按街道名称或门牌号精准排序的实用方法，深入解析其优势与适用场景，并针对不同用户需求提供故障排查技巧与替代解决方案。

在 Excel 中使用辅助列按街道名称对地址进行排序

在 Excel 中使用辅助列按街道编号对地址进行排序

使用 VBA 自动提取并按街道名称或编号对地址进行排序

使用 Power Query 按街道名称或编号对地址进行排序（无需辅助列）

在 Excel 中使用辅助列按街道名称对地址进行排序

要在 Excel 中按街道名称对地址进行排序，首先需将街道名称提取到辅助列中。当地址格式统一（例如“123 Apple St”）时，这种方法简单高效，非常适合快速项目或简单的地址列表。

1. 请选择紧邻地址列表的空白列，并在辅助列的首个单元格中输入以下公式，以提取街道名称：

=MID(A1,FIND(" ",A1)+1,255)

（此处 A1 指您地址数据上方的单元格——若数据起始位置不同，请相应调整。）
输入公式后，按 Enter，然后向下拖动填充柄，将公式应用到整个地址范围。该公式通过查找每个地址中的第一个空格，返回该空格之后的所有内容（即街道名称及任何后缀）。请确保您的地址结构一致，否则公式可能无法按预期正确拆分。

使用公式按街道名称排序地址的截图

2. 选中整个辅助列（即包含提取出的街道名称的列），然后转到数据选项卡，单击升序，即可按字母顺序对街道名称进行升序排序。

使用公式按街道名称排序地址的步骤2：排序截图

3. 在弹出的排序警告对话框中，选择扩展选定区域，即可确保排序时完整的地址信息保持在一起。

使用公式按街道名称排序地址的步骤3：扩展选择区域截图

4. 单击排序，您的地址列表将立即根据街道名称重新排序，使相同街道的地址集中显示，便于查看与管理！

使用公式按街道名称排序地址的结果截图

注意：此方法最适合标准化的地址格式。若地址单元格中包含不规则模式，或街道名称前存在多个空格，则可能需要调整公式。使用公式后，请务必核对部分结果以确保准确无误。

优点：操作简单，无需额外工具。
缺点：依赖格式一致性；若地址格式不统一，则需额外处理。

在 Excel 中使用辅助列按街道编号对地址进行排序

如果您需要按街道编号（例如用于安排配送顺序或识别相邻地址）对地址列表进行排序，可轻松提取其中的数字并据此排序——即使地址分布在不同街道，此方法依然有效。

1. 在紧邻地址列表的空白单元格中，输入以下公式，即可提取街道编号：

=VALUE(LEFT(A1,FIND(" ",A1)-1))

（其中 A1 是列表中的第一个地址——请根据需要调整。）输入后按 Enter。该公式通过定位第一个空格，提取其前的字符并转换为数值。如果您的地址以街道编号（数字）开头，此公式将正确运行。随后，向下拖动填充柄，即可将公式应用到列表其余部分。

使用另一种公式按街道名称排序地址的截图

2. 选中刚创建的辅助列，转到数据选项卡，单击升序（或在较新版本的 Excel 中单击）排序小到大）。

使用另一种公式按街道名称排序地址的步骤2：排序截图

3. 在排序警告对话框中，选择扩展选定区域，即可对整行进行排序。

使用另一种公式按街道名称排序地址的步骤3：扩展选择区域截图

4. 单击排序以应用排序，您的地址将立即按提取出的街道编号排序。

使用另一种公式按街道名称排序地址的结果截图

提示：如果您希望将街道编号保留为文本，或不需要进行数值排序，也可以使用：

=LEFT(A1,FIND(" ",A1)-1)

此版本会将数字提取为文本字符串。

注意事项：如果地址以文字而非数字开头（例如“Main Street 5”），这些公式将无法按预期正常运行。使用前，请务必仔细检查您的地址数据。

优点：地址格式简单时，操作快速且用户友好。
缺点：无法处理街道编号前带名称或后缀的地址，也无法处理包含多个数字的地址。

VBA 代码 —— 通过宏自动提取街道名称/编号并对列表进行排序

对于需要处理较大或更复杂的地址列表，或数据中包含可变地址结构的用户来说，使用 VBA 自动化排序过程极为高效。VBA 能助您快速提取街道名称或编号，并自动完成地址列表排序，大幅减少手动操作。当您需定期执行排序任务，或将排序无缝集成到现有工作流中时，这一解决方案尤为适用。

注意：此 VBA 宏可从 A 列中的每个地址提取街道名称（即第一个空格之后的部分），并据此对整个列表进行排序。只需稍作调整，即可用于提取并按街道编号排序。

1. 单击开发工具选项卡 >Visual Basic。在弹出的窗口中，单击插入 > 模块，并将以下 VBA 代码粘贴到模块窗口中：

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 Sub

2. 要运行代码，请在地址列表处于活动状态时，单击运行按钮按钮或按 F5 键。A 列中的地址列表将立即按街道名称以字母顺序排列。

此版本仅提取首个空格前的数字，并按数值大小排序。

故障排除：
- 请确认地址位于 A 列，或根据您的数据位置更新代码。
- 如果您的数据包含标题行，可将 Header = xlYes 调整为避免对标题行排序。
- 运行批量 VBA 代码前，务必备份数据！

优点：无需辅助列，轻松应对大型数据集或重复排序。
缺点：初始设置需启用宏权限，并具备基础 VBA 知识。

其他内置 Excel 方法 —— 使用 Power Query 拆分地址列并在 Power Query 中直接排序，无需辅助列

Power Query 在现代 Excel 版本（Excel 2016 及更高版本，以及 Microsoft 365）中可用，为您提供了一种灵活且无需公式的高效方式，轻松将地址拆分为街道编号、街道名称等组成部分。如果您希望避免使用公式和辅助列，或面对格式多变的地址数据而基础公式难以高效应对，这一方案尤为理想。此外，Power Query 还能自动保存您的操作步骤，方便在数据更新或扩展时一键刷新，持续保持结果准确一致。

1. 选择您的地址数据，转到数据选项卡，然后点击来自表格/区域（如提示，请先将数据转换为表格）。
2. 在 Power Query 窗口中，选中地址列，单击拆分列 > 按分隔符，选择空格作为分隔符，并将“拆分位置”类型设为最左侧的第一个分隔符。
3. 地址将被拆分为两列：街道号码与剩余的街道名称/地址，如有需要，请重命名新列。
4. 要排序，请点击街道名称或街道号码列标题中的下拉箭头，选择升序排序或降序排序。
5. 单击关闭并加载，即可将排序后的结果载入回您的工作表中。

额外提示：

  • 如果您的地址格式不一致，可以进一步在 Power Query 中使用自定义拆分或转换来操作列。
  • Power Query 会自动记录操作步骤，源数据一旦更新，您即可轻松刷新获取最新数据。
  • 此方法不会改动原始数据，有效提升原始记录的安全性。

优点：不会永久更改您的工作表；轻松应对复杂的地址格式；无需手动管理公式。
缺点：需使用 Excel 2016 或更高版本；界面可能对新用户稍显陌生。

总结与故障排除建议：
- 在应用公式或 VBA 前，请务必检查地址格式的一致性。
- 始终预览排序结果以确认其正确性，尤其是在使用辅助列或代码之后。
- 对于结构异常的数据（例如缺少门牌号，或街道名称位于末尾），请调整公式，或考虑使用 Power Query 进行更稳健的拆分。
- 在使用 VBA 或高级数据工具前，请定期备份数据，以防意外丢失。
- 请选择最适合您数据量、Excel 版本以及工具熟悉程度的解决方案（公式、VBA 或 Power Query）。
- 若不确定哪种方法最佳，Power Query 通常提供最大的灵活性，且在非破坏性编辑方面最为安全。

相关文章：

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱