Remove space between a number (random) and the semicolon ( ; )

Question

With Find and Replace, I want to remove a space between a number (random) and semicolon ( ; )

E.g. 1025648950 ; to 1025648950;

However, if there is no number there, I want the space to remain. I don't know how to use wildcards properly in Word Find & Replace.

This will be going straight into VBA editor.

With Selection.Find
  .Text = "[0-9] ;"
  .Replacement.Text = "[0-9];"
  .Forward = True
  .Wrap = wdFindStop
  .Format = False
  .MatchCase = False
  .MatchWholeWord = False
  .MatchWildcards = False
  .MatchSoundsLike = False
  .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Admin · Answer

You need to do at least three things.

Specify that it is a wildcard search by changing

  .MatchWildcards = False

to

  .MatchWildcards = True

"Group" the Find what expression so that it looks like this

  .Text = "([0-9]) (;)"

(There is one space between the ") (" )

Change the "Replace With expression so it looks like this:

  .Replacement.Text = "\1\2"

How it works is that each search term inside () is an expression which is given a number 1, 2, ...

In the .Replacement.Text, "\1" means "use the text that expression 1 matched"

So "\1\2" means "use the digit that ([0-9]) matched followed by the ";" that (;) matched, so that eliminates the space in the middle.

user_5835002 · Answer

Normally this would be easy using RegEx (regular expressions) but in your case it's more complicated because you want to replace " ;" if it's proceeded by a number but leave it if it's not. The only way I found to do this was by breaking the text down into a character collection then looping through the collection:

Sub RemoveSpaces()
    Dim s As String
    s = Selection
    s = cleanSpaces(s)
    Selection = s
End Sub

Function cleanSpaces(s As String) As String
On Error Resume Next
    Dim regexNums As New RegExp
    Dim charCol As New Collection
    regexNums.Pattern = "[0-9]"

    'create collection with all characters from string.
    For Counter = 1 To Len(s)
        charCol.Add (Mid(s, Counter, 1))
    Next
    'loop through collection and remove number followed by space followed by semicolon.
    For i = 1 To charCol.Count
        If regexNums.Test(charCol(i)) Then 'if number
            If (charCol(i + 1) = " ") Then 'if next character is a space
                If (charCol(i + 2) = ";") Then 'if next char is a semicolon
                    charCol.Remove (i + 1) 'remove the space
                End If
            End If
        End If
    Next

    For i = 1 To charCol.Count
        cleanSpaces = (cleanSpaces & charCol(i))
    Next
End Function

Note that this will only run on the selected text in your document. If you want to run it on the entire document then press ctrl+A to select all the text. Here is my test data before and after:

Remove space between a number (random) and the semicolon ( ; )

2 Answers