Microsoft Office Forums Find & Replace to insert thousands separators

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-18-2019, 08:25 AM
jeffreybrown jeffreybrown is offline Find & Replace to insert thousands separators Windows 10 Find & Replace to insert thousands separators Office 2016
Expert
Find & Replace to insert thousands separators
 
Join Date: Apr 2016
Posts: 568
jeffreybrown will become famous soon enoughjeffreybrown will become famous soon enough
Default Find & Replace to insert thousands separators

I can't quite get the syntax right for replacing a number with the thousand separator.



Before
1266.89
3479.52

After
1,266.89
3,479.52

I tried
F/R with wildcard
Find: ([!/-//][0-9])([0-9]{4})(.[0-9]{2}))>
Replace: \1,\2
Reply With Quote
  #2  
Old 10-18-2019, 05:29 PM
pike pike is offline Find & Replace to insert thousands separators Windows 7 64bit Find & Replace to insert thousands separators Office 2010
Novice
 
Join Date: Jul 2019
Location: Alstonville
Posts: 4
pike is on a distinguished road
Default

maybe just

Code:
Option Explicit
Sub FormatNumbers()
    Dim lngdb As Long
    If Selection.Type = wdSelectionIP Then
        ActiveDocument.Range(0, 0).Select
        lngdb = wdFindContinue
    Else
        lngdb = wdFindStop
    End If
    With Selection.Find
        .ClearFormatting
        .Text = "[0-9]{4,}"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = lngdb
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
        Do While .Execute
            Selection.Text = Format$(Selection.Text, "#,##0")
            If lngdb = wdFindContinue Then
                Selection.Collapse wdCollapseEnd
            Else
                Exit Sub
            End If
        Loop
    End With
End Sub

Last edited by pike; 10-18-2019 at 08:11 PM.
Reply With Quote
  #3  
Old 10-19-2019, 06:49 AM
jeffreybrown jeffreybrown is offline Find & Replace to insert thousands separators Windows 10 Find & Replace to insert thousands separators Office 2016
Expert
Find & Replace to insert thousands separators
 
Join Date: Apr 2016
Posts: 568
jeffreybrown will become famous soon enoughjeffreybrown will become famous soon enough
Default

Thank you Pike. This will do it.
Reply With Quote
  #4  
Old 10-19-2019, 04:37 PM
pike pike is offline Find & Replace to insert thousands separators Windows 7 64bit Find & Replace to insert thousands separators Office 2010
Novice
 
Join Date: Jul 2019
Location: Alstonville
Posts: 4
pike is on a distinguished road
Default

no problem. Sometimes RegExp match and replace is easier than the word F/R
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find & Replace to insert thousands separators Find and Replace/Insert 'CR', delete leading Spaces wherever a user Clicks in a Word document DavidL Word VBA 7 04-04-2018 12:01 AM
Find & Replace to insert thousands separators Find and Replace - How to insert brackets around all numbers in a doc Natedogg Word 2 05-21-2015 07:16 AM
Find & Replace to insert thousands separators Insert text at the end of a sentence Find/Replace AlmostFriday Word 6 06-17-2012 05:21 AM
Find & Replace to insert thousands separators Bad view when using Find and Find & Replace - Word places found string on top line paulkaye Word 4 12-06-2011 11:05 PM
Find & Replace Insert Issue mipacker Word 0 02-18-2009 08:59 AM


All times are GMT -7. The time now is 01:06 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft