Consulting

Results 1 to 5 of 5

Thread: Remove Spaces After Coma If Preceded By A Number

  1. #1
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location

    Remove Spaces After Coma If Preceded By A Number

    I'm struggling to get this Find / Replace to work. It's to try and remove any spaces after a comma when monetry values are found.

    Such as this £45, 000 should become thus £45,000 and
    £125, 643 should become thus £125,643

    Everything I have tried so far either leaves things as per the original state, or removes the preceding numbers and one of the trailing numbers.

    Here's what I have:-

    Private Sub FixSpacing(CCtrl As ContentControl)
        Dim oRng    As Range
    
        With CCtrl
            Select Case .Type
                Case wdContentControlText, wdContentControlRichText
                    Set oRng = .Range
                    With oRng
                        ' Remove trailing spaces
                        Do While .Characters.Last = " "
                            .Characters.Last.Text = vbNullString
                        Loop
                        .End = .End - 1
                        With .Find
                            .ClearFormatting
                            .Replacement.ClearFormatting
                            .Forward = True
                            .Format = False
                            .Wrap = wdFindStop
                            .MatchWildcards = True                      
                            
                            ' Remove single space after comma if it is preceded by a number
                            .Text = "([0-9],[^s])"
                            .Replacement.Text = ","
                            .Execute Replace:=wdReplaceAll
    
                        End With
                    End With
                Case Else
                   End Select
        End With
        
    lbl_Exit:
        Exit Sub
    End Sub

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    One wonders why you're even bothering with a macro when all you need is a simple wildcard Find/Replace, where:
    Find = ([0-9],)[ ^s]([0-9])
    Replace = \1\2
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Many thanks, Paul.

    It's needed in a macro as the template is being used on a daily basis and by many users, some of whom are not even aware of the find / replace capabilities of word. It's trying to reduce as many errors as possible.

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    In which case, you could incorporate the Find/Replace in another macro you can be fairly sure the users will run. If the errors are restricted to content control ranges, you could simply use a Document_ContentControlOnExit macro to do a Find/Replace on each text content control as it is exited.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    This is part of a sub that is executed to produce a complete document, so this and a couple of others will run by default.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •