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
– marineninjas, Commented Apr 10, 2018 at 10:47
See below. Also, \s{2,0} is an invalid pattern, just try it at regex101.com — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Apr 10, 2018 at 10:49

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

5

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.

answered Apr 10, 2018 at 10:46

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HackSlash · Accepted Answer · 2023-11-29 19:54:10Z

1

The accepted answer doesn't work with multi-line strings because it smashes newline characters too.

If you need to only crush multiple spaces, like the question title, then use this:

''' Replace multiples spaces with one, because one is enough.
Public Function CrushSpaces(ByVal text As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = " {2,}"
        .Global = True
        .MultiLine = True
        
        CrushSpaces = .Replace(text, " ")
    End With
End Function

answered Nov 29, 2023 at 19:54

HackSlash

6,0372 gold badges28 silver badges59 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related