Loop and replace two or more spaces with only one

Question

I want to replace all multiple spaces in cells in my range A1:K1000. So if 2 or more spaces, then replace them with one space " ".

My current code replaces spaces that are " " (2) spaces. How to adjust it to work with 2 or more spaces? So it will handle cases like " ", " ", " " etc.

Here is my code:

Sub RemoveSpaces()

Dim r1 As Range
Set r1 = ActiveSheet.Range("A1:K1000") 
r1.Replace _
      What:=Space(2), _
      Replacement:=" ", _
      SearchOrder:=xlByColumns, _
      MatchCase:=True
Set r1 = r1.Find(What:=Space(2))
If Not r1 Is Nothing Then
   Call RemoveSpaces
End If
End Sub

Possible duplicate of [Fastest way to remove 'extra' spaces (more than 1) from a large range of cells using VBA for excel](https://stackoverflow.com/questions/30768072/fastest-way-to-remove-extra-spaces-more-than-1-from-a-large-range-of-cells-u) — BigBen, Oct 18 '19 at 15:02
See the recursive approach in the dupe target. Or adjust one of the other answers. In any case, this has been tried before, and multiple solutions have been proposed. — BigBen, Oct 18 '19 at 15:03
Note - change `Call RemoveSpaces_3` to `RemoveSpaces` to recurse (`Call` is deprecated too). — BigBen, Oct 18 '19 at 15:06
Looks like duplicate but actually in provided link there is only particular case covered with replace. I already have this kind of code that is covering case with two spaces. However I need to remove all multiple spaces -> two and more. Scott Craner solution looks good. I have to try it — 10101, Oct 18 '19 at 15:07
Where are these spaces? i.e. beginning of the text or end? or can be either or both? — Zac, Oct 18 '19 at 15:12
They can be in the begging and at the end, sometimes they are between the words — 10101, Oct 18 '19 at 15:13
[This might help](https://stackoverflow.com/questions/7777419/expression-to-test-for-one-or-more-spaces-in-vba-to-find-matches-in-excel-cells) — Zac, Oct 18 '19 at 15:20
The *recursive* answer in the dupe handles more than two spaces. — BigBen, Oct 18 '19 at 15:27

score 3 · Accepted Answer · answered Oct 18 '19 at 15:22

3

There is no need to loop cells:

Sub Test()

Dim rng As Range
Set rng = Sheets("Sheet1").Range("A1:A3")
rng.Value = Application.Trim(rng)

End Sub

answered Oct 18 '19 at 15:22

JvdV

70,606
8
39
70

1

This should probably be an answer to the dupe target too, this is great. – BigBen Oct 18 '19 at 15:30
I don't know if it should be posted there, but at an initial glance it seems better than the accepted answer. Note, this is definitely a dupe, despite claims to the contrary :) – BigBen Oct 18 '19 at 15:32
Although... Replace might be better if formulas are in involved, I still need to test. – BigBen Oct 18 '19 at 15:34
My comment was that it overwrites formulas with values... `="This is a test"` gets converted to text :( Just a caveat. – BigBen Oct 18 '19 at 15:41
1

@BigBen, I see. Agreed! Either way, my answer is up there now too. Will put a note down, that it will overwrite formula results with it's value – JvdV Oct 18 '19 at 15:44

score 0 · Answer 2 · answered Oct 18 '19 at 15:18

0

So from Scott Craner comment, solution to this question seems to be:

Sub RemoveSpaces()

    Dim A As Range

    Set A = ActiveSheet.Range("A1:K1000") 

    For Each cell In A
    cell.Value = WorksheetFunction.Trim(cell)
    Next

End Sub

answered Oct 18 '19 at 15:18

10101

2,232
3
26
66

Loop and replace two or more spaces with only one

2 Answers2