How to replace multiple spaces in VBA with a single space using regex?

Question

I have an account statement that needs to be cleansed before doing some work with SQL. I've used a RegEx pattern to remove any punctuation and thought I could also use this to match excessive whitespace between words but I'm having trouble with the replace section of my VBA script.

So far I have:

   Sub RemoveSpace()
Dim cell As Range
Range("A2:C2").Select
Range(Selection, Selection.End(xlDown)).Select

With CreateObject("vbscript.regexp")
  .Pattern = "[\s{2,0}]"
  .Global = True
  For Each cell In Selection.SpecialCells(xlCellTypeConstants)
    cell.Value = .Replace(cell.Value, " ")
  Next cell
End With
End Sub

Although this script matches the spaces it doesn't replace them with just a single space. I think what happens is that Excel replaces each single space it matches with another space rather than treating multiple spaces as "one". Is there a way I could remove all of the multiple spaces and replace them with a single space?

Thanks, that worked perfectly! Could you just explain why removing the zero and square brackets made a difference? — marineninjas
See below. Also, \s{2,0} is an invalid pattern, just try it at regex101.com — Wiktor Stribiżew

Wiktor Stribiżew Wiktor Stribiżew · Accepted Answer · 2018-04-10T10:46:04

The [\s{2,0}] pattern matches any single whitespace, {, 2, ,, 0 or } chars since these are inside a character class, inside [...]. See the character class reference.

Once you remove [...], you need to also remove the 0 as you need {2,} to match 2 or more occurrences. See the limiting quantifier reference.

Use

.Pattern = "\s{2,}"

See the regex demo.

How to replace multiple spaces in VBA with a single space using regex?

1 Answers