跳至主要内容

如何在Excel中对带有文本/字母前缀或后缀的数字进行排序?

Author: Kelly Last Modified: 2025-05-07

例如,你有一系列包含字母前缀或后缀的数字,如何在Excel中将它们像普通数字一样排序呢?本文收集了几种解决方法来实现这一目标。


通过用户定义函数对带有文本/字母前缀或后缀的数字进行排序

此方法将介绍一个用户定义函数(VBA),用于仅从带有前缀或后缀的数字中提取数字,然后你可以轻松进行排序。

1. 同时按下 Alt + F11 键打开 Microsoft Visual Basic for Applications 窗口。

2. 单击 插入 > 模块,然后将以下 VBA 代码粘贴到新打开的模块窗口中。

用户定义函数:仅从文本字符串中提取数字

Function num(rng As Range) As String
Dim n As Integer
For n = 1 To Len(rng)
If Mid(rng, n, 1) Like "[0-9]" Then
num = num & Mid(rng, n, 1)
End If
Next n
End Function

3. 保存代码并关闭 Microsoft Visual Basic for Applications 窗口。

4选择原始带前缀或后缀数字旁边的一个空白单元格,比如单元格 B2,输入公式 =num(A2) (A2 是带有前缀或后缀的数字)到其中,然后拖动该单元格的自动填充柄至所需范围。
Formula entered in each cell

5由于提取出的数字存储为文本,我们需要将其转换为数字。复制所有提取出的数字,右键单击复制的区域并选择 粘贴值 从右键菜单中。见截图:
Paste Values option on the right clicking menu
注意:在 Excel 2007 中,你可以复制存储为文本的提取数字,然后点击 开始 > 粘贴 > 粘贴值.

6。继续选择这些数字,然后点击 Exclamation mark icon 所选数字旁边的按钮,并从下拉列表中选择 转换为数字 。见截图:
Convert to Number option on the dropdown

7. 继续保持新数字被选中状态,点击 数据 > 从小到大排序从大到小排序;在弹出的排序警告对话框中勾选 扩展选定区域 选项,然后点击 排序 按钮。见截图:

Sort options on the ribbon
Expand the selection option on the Sort Warning dialog

8现在带有字母前缀或后缀的数字已经按照普通数字排序完成(见下图)。如有必要,请删除提取出的数字。
Numbers with letter prefix or suffix are sorted


通过提取数字对带有文本/字母前缀或后缀的数字进行排序

实际上,Kutools for Excel 还提供了类似的 EXTRACTNUMBERS 函数来从文本字符串中提取数字。因此,我们可以通过先提取数字来对带有字母前缀或后缀的数字进行排序。

Kutools for Excel - 包含超过 300 个 Excel 必备工具。永久免费享受 AI 功能!立即下载!

1选择带有字母前缀或后缀数字旁边的一个空白单元格,比如单元格 B2,输入公式 =EXTRACTNUMBERS(A2,TRUE) (A2 是带有字母前缀或后缀数字的单元格)到其中,然后拖动该单元格的自动填充柄至所需范围。见截图:
Formula used to extract numbers from each cell

2. 保持提取出的数字被选中,点击 数据 > 从小到大排序从大到小排序;在弹出的排序警告对话框中勾选 扩展选定区域 选项,然后点击 排序 按钮。见下图:

Sort options on the ribbon
Expand the selection option on the Sort Warning dialog

3. 现在带有字母前缀或后缀的数字已经按照普通数字排序完成。你可以根据需要删除所有提取出的数字。


通过拆分文本和数字对带有文本/字母前缀或后缀的数字进行排序

Kutools for Excel 还支持使用其 拆分单元格 工具从单元格中拆分文本和数字。因此,我们可以将文本和数字拆分开并放置在不同的列中以便于排序。请按以下步骤操作:

Kutools for Excel - 包含超过 300 个 Excel 必备工具。永久免费享受 AI 功能!立即下载!

1选择带有字母前缀或后缀的数字,然后点击 Kutools > 文本 > 拆分单元格。见下图:
Split Cells option on the Kutools tab on the ribbon and the Split Cells dialog

2. 在打开的 拆分单元格 对话框中,请在 类型 部分勾选 拆分为列 选项,在 拆分依据 部分勾选 文本和数字,然后点击 确定 按钮。见上图:

3现在第二个 拆分单元格 对话框弹出。请选择原始带有字母前缀或后缀数字旁边的单元格,比如单元格 B2,然后点击 确定 按钮。
Split Cells dialog for selecting the desitnation cell to paste 

现在文本和数字已被拆分并放置在原始带有前缀或后缀数字的旁边。见下图。
Texts and numbers are split and placed beside original numbers with prefix or suffix

4. 选择拆分后的数字,点击 数据 > 从小到大排序从大到小排序;在弹出的排序警告对话框中勾选 扩展选定区域 选项,然后点击 排序 按钮。见下图:

Sort options on the ribbon
Expand the selection option on the Sort Warning dialog

5. 现在带有字母前缀或后缀的数字已经按照普通数字排序完成。你可以根据需要删除拆分后的文本和数字。


通过删除字母对带有文本/字母前缀或后缀的数字进行排序

此方法将介绍 Kutools for Excel 的 删除特定字符 工具来删除所有非数字字符(包括字母),并在单元格中只保留数字。然后你可以轻松对带有文本/字母前缀或后缀的数字进行排序。

Kutools for Excel - 包含超过 300 个 Excel 必备工具。永久免费享受 AI 功能!立即下载!

1. 复制所有带有字母前缀或后缀的数字,然后粘贴到原始带有字母前缀或后缀数字的旁边。

2保持选中粘贴的数字,点击 Kutools > 文本 > 删除特定字符,然后只勾选 非数字字符 在弹出的 删除特定字符 对话框中的选项,然后点击 确定 按钮。见下图:
Remove Characters option on the Kutools tab on the ribbon and the Remove Characters dialog

3. 现在新列中只剩下数字。保持选中这些数字,点击 数据 > 从小到大排序从大到小排序;在弹出的排序警告对话框中勾选 扩展选定区域 选项,然后点击 排序 按钮。见下图:

Sort options on the ribbon
Expand the selection option on the Sort Warning dialog

4. 根据需要删除数字。


演示:在 Excel 中对带有文本/字母前缀或后缀的数字进行排序

Kutools for Excel:超过 300 种实用工具触手可及!永久免费享受 AI 功能!立即下载!

相关文章

最佳办公效率工具

🤖 Kutools AI 助手:基于智能执行生成代码创建自定义公式分析数据并生成图表调用 Kutools 函数等功能,彻底改变数据分析方式…
热门功能查找、高亮或标记重复项 | 删除空行 | 合并不丢失数据的列或单元格 | 四舍五入 ...
高级 LOOKUP多条件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 ....
高级下拉列表快速创建下拉列表 | 从属下拉列表 | 多选下拉列表 ....
列管理器添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域和列 ...
精选功能网格聚焦 | 设计视图 | 增强编辑栏 | 工作簿与工作表管理器 | 资源库(自动文本) | 日期提取 | 合并数据 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊筛选(筛选粗体/斜体/删除线...)...
排名前 15 的工具集12 种文本 工具添加文本删除特定字符等) | 50 多 种图表 类型甘特图等) | 40 多种实用 公式基于生日计算年龄等) | 19 种插入 工具插入二维码根据路径插入图片等) | 12 种转换 工具小写金额转大写汇率转换等) | 7 种合并与分割 工具高级合并行分割单元格等) | 还有更多...

使用 Kutools for Excel 提升您的 Excel 技能,体验前所未有的高效。 Kutools for Excel 提供超过 300 种高级功能来提高生产力并节省时间。 单击此处获取您最需要的功能...


Office Tab 将标签式界面引入 Office,让您的工作更加轻松

  • 在 Word、Excel、PowerPoint 中启用标签式编辑和阅读
  • 在同一窗口的新标签页中打开和创建多个文档,而不是在新窗口中进行操作。
  • 将您的生产力提升 50%,每天为您减少数百次鼠标点击!