跳至主要内容

Kutools for Office — 一套工具，五种功能。事半功倍。

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

Author Sun Last modified

当您在Excel中管理地址列表时，通常需要通过按街道名称或街道号码来组织或分析数据。例如，如果您需要将住在同一条街上的客户归为一组，或者必须按门牌号顺序处理送货，那么按这些组成部分排序是必不可少的。然而，由于典型的地址格式在一个单元格内混合了街道名称和号码，直接排序无法得到预期结果。在本文中，我们将讨论在Excel中按街道名称或街道号码对地址进行排序的实际方法，分析它们的优点和应用场景，并针对各种用户需求提供故障排除和替代解决方案。

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

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

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

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

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

要在Excel中按街道名称对地址进行排序，首先需要将街道名称提取到一个辅助列中。这种方法简单明了，在地址格式一致时效果很好，例如“苹果街123号”。它适用于快速项目或简单的地址列表。

1. 选择地址列表旁边的一个空列。在辅助列的第一个单元格中输入以下公式以提取街道名称：

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

（这里，A1指的是地址数据的顶部单元格——如果您的数据从其他地方开始，请调整。）
输入公式后，按下 Enter 然后拖动填充柄向下应用公式到地址范围的所有行。该公式通过查找每个地址中的第一个空格，然后返回空格后面的所有内容——街道名称及其任何后缀。确保您的地址遵循相同的结构；否则，公式可能无法正确拆分。

a screenshot of sorting addresses by street name with formula

2. 突出显示整个辅助列（包含提取的街道名称的列），然后转到“数据”选项卡并点击“升序排序”。这将按字母顺序（升序）对街道名称进行排序。

a screenshot of sorting addresses by street name with formula step2 sort

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

a screenshot of sorting addresses by street name with formula step3 expand selection

4. 单击“排序”。现在，您的地址列表将根据街道名称重新排序，使相似的街道排列在一起。

a screenshot of sorting addresses by street name with formula result

注意：此方法最适合标准化地址格式。如果您的地址单元格包含不规则模式或街道名称前有多个空格，则可能需要调整公式。始终检查一些结果以确保准确性。

优点： 简单，不需要额外工具。
缺点： 依赖于一致的格式；如果地址格式不同，需要额外的工作。

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

如果您需要按数字街道号码对地址列表进行排序——例如分配送货顺序或识别相邻地址——即使地址位于不同的街道上，提取号码并用于排序也很容易且有效。

1. 在地址列表旁边的空白单元格中，输入以下公式以提取街道号码：

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

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

a screenshot of sorting addresses by street name with formula2

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

a screenshot of sorting addresses by street name with formula2 step2 sort

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

a screenshot of sorting addresses by street name with formula2 step3 expand selection

4. 单击“排序”以应用。现在，您的地址已按提取的街道号码排序。

a screenshot of sorting addresses by street name with formula2 result

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

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

此版本将提取的号码作为文本字符串。

注意事项：如果地址以单词而不是数字开头（例如“Main Street5”），这些公式将无法按预期工作。在使用公式之前，请仔细检查您的地址数据。

优点： 如果地址格式简单，快速且用户友好。
缺点： 无法处理地址中名字/后缀在号码前的情况，也无法处理包含多个数字的地址。

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要运行代码，请在地址列表活动的情况下，单击 Run button 按钮或按 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通常提供最大的灵活性，并且对非破坏性编辑是最安全的。

相关文章：

最佳Office办公效率工具

🤖 Kutools AI 助手：以智能执行为基础，彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数……
热门功能：查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入……
高级LOOKUP：多条件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中进行多标签编辑与阅读。
  • 在同一个窗口的新标签页中打开和创建多个文档，而不是分多个窗口。
  • 可提升50%的工作效率，每天为您减少数百次鼠标点击！

所有Kutools加载项，一键安装

Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件，以及Office Tab Pro，非常适合跨Office应用团队使用。

Excel Word Outlook Tabs PowerPoint
  • 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
  • 单一安装包、单一授权——数分钟即可完成设置（支持MSI）
  • 协同更高效——提升Office应用间的整体工作效率
  • 30天全功能试用——无需注册，无需信用卡
  • 超高性价比——比单独购买更实惠