Faster way to remove 'extra' spaces (more than 1) from a large range of cells using VBA for Excel

Question

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

Let's say 5000+ cells.

Some ways I have tried include:

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

and

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

and

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

Any ideas for speed improvement?

Do you have to use VBA? You might get performance improvements by operating on the spreadsheet externally using .Net, Python, Perl, etc. — David, Jun 10 '15 at 22:07
why dont you use the trim function directly on the spreadsheet? should be significantly faster. — TylerDurden, Jun 10 '15 at 22:16

JvdV · Answer 1 · 2019-10-18T15:55:35.580

22

Late to the party but...

There is no need for iteration through cells/values nor a recursive function to search and replace multiple spaces in a range.

Application.Trim wil actually take care of multiple spaces between words (and will trim leading/trailing spaces) leaving single spaces in between words intact.

The great thing about it, is that you can feed the function a full range (or array) to do this operation in one sweep!

Sub Test()

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

End Sub

The one thing to take into consideration is that this way you'll overwrite any formulas sitting in your target range with its value. But as per your question, you working with a Range object containing text values. There was just no need for iteration =)

edited Oct 18 '19 at 15:55

answered Oct 18 '19 at 15:37

JvdV

70,606
8
39
70

2

From the VBA Trim() function, I would never guess that the Application.Trim function would work differently. Thank you :) – sergio trajano Jan 04 '21 at 18:16
2

:+) for showing a mostly disregarded way @JvdV – T.M. Feb 05 '21 at 20:14
3

++ yeah agree with @T.M I rarely see people using this. :) – Siddharth Rout Mar 01 '21 at 21:43
I might've encountered a bug with this approach. In one of my worksheets, I selected multiple cells with Ctrl + Click. Excel wiped out everything and returned #VALUE! All the data are now gone :( – Huy Truong Aug 18 '21 at 18:35
@HuyTruong. You probably responded to the wrong thread. – JvdV Aug 18 '21 at 20:38
@JvdV. Sorry, I should have explained clearer. I took your solution and tweaked it a little bit. What I wanted was to apply `Application.Trim` to a range of selected cells, which fed into the routine/function via `Application.InputBox`. When the dialog box popped up, I did Ctrl + Click for a few cells, hit OK, and got the return values as #VALUE! – Huy Truong Aug 19 '21 at 04:20
1

OP probably should have marked this as answer, depending on whether or not he needed to keep formulas intact. I had a macro which took 8 minutes to loop through maybe 100k cells and trim them. Removing the loop and adding `rng.value = Application.Trim(rng)` brought the execution time down to 22 seconds, with only a couple seconds of that being the trim function. – Sabatino Ognibene Mar 01 '22 at 17:52

Gary's Student · Accepted Answer · 2015-06-11T22:37:01.607

5

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

EDIT#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

EDIT#2:

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

edited Jun 11 '15 at 22:37

answered Jun 10 '15 at 22:19

Gary's Student

95,722
10
59
99

Gary - this is great except I was hoping for one that removes all the extra spaces and brings it to a single space. 2 to 1, 3 to 1. Like the trim function within excel. – walter's human Jun 11 '15 at 20:27
very nice! could I also use a "do while" to automate? – walter's human Jun 11 '15 at 21:17
1

Change MsgBox "please run again" to SpaceKiller3 and it will run until there are none. – Dan Donoghue Jun 11 '15 at 22:00
@DanDonoghue **YOU ARE A GENIUS !** I forgot about doing it recursively! – Gary's Student Jun 11 '15 at 22:32
1

It was a quick fix, I would probably go with a Do until r is nothing for a final solution as opposed to self referencing, It's only personal opinion but I am not a fan of subs calling themselves. Great code BTW Gary's Student, I didn't think of using a replace functionality :). Also on a side note, you don't need "Call" there, just the sub name will suffice :). – Dan Donoghue Jun 11 '15 at 22:44

kulapo · Answer 3 · 2017-05-15T15:03:31.813

3

I'm usually using Evaluate than loops when it comes on large range. There are so many use of this function, but i won't discuss it further here.

'change the row count as deemed necessary..
Set rng = Range("C1:C" & Row.Count)

   rng.value = Evaluate("IF(" & rng.Address & "<>"""", _
               TRIM(" & rng.Address & "),"""")")

Set rng = Nothing

edited May 15 '17 at 15:03

answered May 02 '17 at 08:02

kulapo

397
3
15

1

this should be the fastest, but Worksheet.Evaluate is a bit faster than Application.Evaluate `rng = rng.Worksheet.Evaluate("index(trim(" & rng.Address & "),)")` – Slai Sep 15 '17 at 12:10
Would you mind explaining how does evaluate get to iterate or expand upon the multi-cell range? I suppose it would be part of the internal working of "Evaluate". Anyway, your answer was a total life saver (which works as plug and play). You could replace the range with active selection and turn in into a button. – ferreiradev Aug 09 '20 at 16:39

score 2 · Answer 4 · answered Sep 18 '17 at 12:05

It can depend on many things, but in my case fastest was to get all values at once in array:

' Dim range As Range, r As Long, c As Long, a
a = range
For r = 1 To UBound(a)
    For c = 1 To UBound(a, 2)
        a(r, c) = Trim(a(r, c))
    Next
Next
range = a

score 1 · Answer 5 · answered Jun 10 '15 at 22:20

1

Do you have a spare column next to it?

Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=Trim(A1)"
Columns(2).copy
Range("B1").PasteSpecial xlPasteValues
Columns(1).delete

answered Jun 10 '15 at 22:20

Dan Donoghue

6,056
2
18
36

@dan_Donoghue this actually works when needing to remove all spaces after and the extra spaces in between. also very useful. thanks – walter's human Jun 12 '15 at 19:36

Faster way to remove 'extra' spaces (more than 1) from a large range of cells using VBA for Excel

5 Answers5

Linked