使用 VBA for Excel 从大量单元格中删除“额外"空格(超过 1 个)的更快方法 [英] Faster way to remove 'extra' spaces (more than 1) from a large range of cells using VBA for Excel

问题描述

如何更快地从包含文本字符串的大量单元格中删除多余的空格?

How do I remove extra spaces faster, from a large range of cells containing text strings?

假设有 5000 多个单元格.

Let's say 5000+ cells.

我尝试过的一些方法包括:

Some ways I have tried include:

For Each c In range
    c.Value = Trim(c.Value)
Next c

和

For Each c In range
    c = WorksheetFunction.Trim(c)
Next c

和

For Each c In range
    c.Value = Replace(c.Value, "     ", " ")
Next c

有什么提高速度的想法吗?

Any ideas for speed improvement?

推荐答案

循环正在扼杀你.这将一次性删除整列中的空格:

The loop is killing you. This will remove spaces in an entire column in one shot:

Sub SpaceKiller()
   Worksheets("Sheet1").Columns("A").Replace _
      What:=" ", _
      Replacement:="", _
      SearchOrder:=xlByColumns, _
      MatchCase:=True
End Sub

调整范围以适应.如果要删除双空格，则:

Adjust the range to suit. If you want to remove double spaces, then:

Sub SpaceKiller()
   Worksheets("Sheet1").Columns("A").Replace _
      What:="  ", _
      Replacement:=" ", _
      SearchOrder:=xlByColumns, _
      MatchCase:=True
End Sub

编辑#1:

此版本将双打替换为单打，然后检查是否还有双打！

This version will replace doubles with singles and then check if there are still still doubles left!

Sub SpaceKiller3()
   Worksheets("Sheet1").Columns("A").Replace _
      What:="  ", _
      Replacement:=" ", _
      SearchOrder:=xlByColumns, _
      MatchCase:=True

   Set r = Worksheets("Sheet1").Columns("A").Find(What:="  ")
   If r Is Nothing Then
      MsgBox "done"
   Else
      MsgBox "please run again"
   End If
End Sub

您可以重新运行，直到看到完成

You can re-run until you see done

编辑#2:

根据Don Donoghue的评论，此版本将递归运行，直到所有双精度转换为单精度:

based on Don Donoghue's comment, this version will run recursively until all double are converted to singles:

Sub SpaceKiller3()
   Worksheets("Sheet1").Columns("A").Replace _
      What:="  ", _
      Replacement:=" ", _
      SearchOrder:=xlByColumns, _
      MatchCase:=True

   Set r = Worksheets("Sheet1").Columns("A").Find(What:="  ")
   If r Is Nothing Then
      MsgBox "done"
   Else
      Call SpaceKiller3
   End If
End Sub

这篇关于使用 VBA for Excel 从大量单元格中删除“额外"空格(超过 1 个)的更快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！