如何用VBA去除Excel多行单元格冗余空格并按数字排序内容

用于处理单元格内多行格式为FixedPrefix_Number的内容，完成移除冗余空格+按数字部分自然排序（而非字典序）的需求。

完整VBA代码 #

Sub ProcessAndSortCells()
    Dim targetCell As Range
    Dim cellText As String
    Dim textLines() As String
    Dim processedLines As Collection
    Dim lineItem As Variant
    Dim splitParts() As String
    Dim cleanLine As String
    Dim sortArray() As Variant
    Dim i As Integer, j As Integer
    Dim temp As Variant
    
    ' 遍历选中的每个单元格
    For Each targetCell In Selection
        cellText = targetCell.Value
        If cellText <> "" Then
            ' 拆分多行内容（按换行符分割）
            textLines = Split(cellText, vbLf)
            Set processedLines = New Collection
            
            ' 逐行处理：移除冗余空格，提取有效内容
            For Each lineItem In textLines
                cleanLine = Trim(lineItem) ' 移除首尾空格
                If cleanLine <> "" Then ' 跳过空行
                    ' 拆分前缀和数字部分
                    splitParts = Split(cleanLine, "_")
                    If UBound(splitParts) = 1 And IsNumeric(splitParts(1)) Then
                        ' 存储为数组：(数字, 完整内容)，方便排序
                        processedLines.Add Array(CLng(splitParts(1)), cleanLine)
                    Else
                        ' 格式不符合的内容直接保留
                        processedLines.Add Array(0, cleanLine)
                    End If
                End If
            Next lineItem
            
            ' 将集合转为数组用于排序
            ReDim sortArray(1 To processedLines.Count)
            For i = 1 To processedLines.Count
                sortArray(i) = processedLines(i)
            Next i
            
            ' 按数字部分升序排序（冒泡排序，简单易实现）
            For i = LBound(sortArray) To UBound(sortArray) - 1
                For j = i + 1 To UBound(sortArray)
                    If sortArray(i)(0) > sortArray(j)(0) Then
                        temp = sortArray(i)
                        sortArray(i) = sortArray(j)
                        sortArray(j) = temp
                    End If
                Next j
            Next i
            
            ' 重新拼接成多行文本
            cellText = ""
            For i = LBound(sortArray) To UBound(sortArray)
                cellText = cellText & sortArray(i)(1) & vbLf
            Next i
            ' 移除最后多余的换行符
            targetCell.Value = Left(cellText, Len(cellText) - 1)
        End If
    Next targetCell
End Sub

关键功能说明 #

• 移除冗余空格：用Trim()函数清除每行首尾的空格，同时跳过处理后为空的行
• 数字提取与排序：
  • 按_拆分每行内容，提取后缀的数字部分转为长整型
  • 以(数字值, 完整行内容)的数组形式存储，再通过冒泡排序按数字值升序排列
  • 格式不符合FixedPrefix_Number的行会被放在排序结果的最前面（数字值设为0）
• 批量处理：支持同时选中多个单元格进行批量处理

使用步骤 #

1. 打开Excel文件，选中需要处理的单元格/单元格区域
2. 按下Alt + F11打开VBA编辑器
3. 右键点击左侧项目窗口中的当前工作簿，选择「插入」→「模块」
4. 将上述代码粘贴到模块窗口中
5. 按下F5运行宏，或者回到Excel界面通过「开发工具」→「宏」选择ProcessAndSortCells执行

内容的提问来源于stack exchange，提问作者Aleph0