h1

Dot Comma Boom

November 26, 2010

Vista busy cursor In continental Europe they don’t use a decimal point, they have a decimal comma instead. And conversely (if not perversely), they use full stops, not commas, to split large numbers into groups of three digits. So in a set of accounts instead of say 10,287,671.45 you would see 10.287.671,45.

I recently had occasion to use figures from a European report in a Word document for UK consumption so wanted the commas and dots switched round. Maybe Word has an automatic facility for doing this. If so I haven’t found it but reckoned it would be easy enough to write a quick VBA macro.

As ever, I try to save time by using the built in macro recorder. First problem is that in Word once the macro recorder is running you cannot select any text on the document. Now that is a bit of a problem because you want to be able to select just the table or column of numbers which need commas/dots switching, otherwise all the full stops in the main part of the text end up as commas which would not be good.

I gave up on the macro recorder and found some VBA code snippets on the web which I thought I could adapt to do the job. And indeed it is easy enough to find something like this:

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
     .Text = "."
     .Replacement.Text = ","
     .Forward = True
     .Wrap = wdFindStop
     .Format = False
     .MatchCase = False
     .MatchWholeWord = False
     .MatchWildcards = False
     .MatchSoundsLike = False
     .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Put this in a VBA subroutine and it will replace all full stops in the selected text with commas. But of course, you want to swap commas and dots which actually takes three steps. You need to replace full stops with commas but you can’t do that immediately because if you did then you could not distinguish them from the commas which were already in the text which need to be converted to full stops! Instead, you replace the full stops with a “third” character (I used a question mark) so that you can then replace all commas with full stops without confusion. Finally, you convert the question marks to commas and the job is done.

The code looks like this:

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
     .Text = "."
     .Replacement.Text = "?"
     .Forward = True
     .Wrap = wdFindStop
     .Format = False
     .MatchCase = False
     .MatchWholeWord = False
     .MatchWildcards = False
     .MatchSoundsLike = False
     .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
     .Text = ","
     .Replacement.Text = "."
     .Forward = True
     .Wrap = wdFindStop
     .Format = False
     .MatchCase = False
     .MatchWholeWord = False
     .MatchWildcards = False
     .MatchSoundsLike = False
     .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
     .Text = "?"
     .Replacement.Text = ","
     .Forward = True
     .Wrap = wdFindStop
     .Format = False
     .MatchCase = False
     .MatchWholeWord = False
     .MatchWildcards = False
     .MatchSoundsLike = False
     .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

But there’s still a problem. Very annoyingly, after the first block of code (dot to question mark) is completed the text selection is lost. You can see this if you put a breakpoint in the code and look at the Word document. What then happens is that the second block of code does not stop at the end of the original selection and proceeds to convert commas to dots throughout the rest of the document.

It took me a while to figure that one out. I reckoned that if I could find a way to remember the original selection in a variable, I could keep reselecting the same block of text before the next replacement operation thereby getting round the problem. How exactly to do that was the hard bit but I eventually discovered how to do it with a variable of type “range”. I had been trying a variable of type “Selection” which seemed more obvious but actually led nowhere.

This is the final complete code for the comma dot exchange subroutine.

Sub DotCommaBoom()

Dim myRng As Range

Set myRng = Selection.Range.Duplicate

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
     .Text = "."
     .Replacement.Text = "?"
     .Forward = True
     .Wrap = wdFindStop
     .Format = False
     .MatchCase = False
     .MatchWholeWord = False
     .MatchWildcards = False
     .MatchSoundsLike = False
     .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

myRng.Select

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
     .Text = ","
     .Replacement.Text = "."
     .Forward = True
     .Wrap = wdFindStop
     .Format = False
     .MatchCase = False
     .MatchWholeWord = False
     .MatchWildcards = False
     .MatchSoundsLike = False
     .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

myRng.Select

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
     .Text = "?"
     .Replacement.Text = ","
     .Forward = True
     .Wrap = wdFindStop
     .Format = False
     .MatchCase = False
     .MatchWholeWord = False
     .MatchWildcards = False
     .MatchSoundsLike = False
     .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

End Sub
Advertisements

3 comments

  1. Thank you for this. As a German > English translator I often need to translate financial statements with figures that need “converting”. I wasn’t able to use the code that you kindly provided, because I lack the know-how to integrate it into MS Word, but your suggestion with using the question mark as an interim step has made my life very much easier!!! Thanks again.


    • Glad to have been of help Daniela. Adding the code to MS Word is not too hard. If I can find a bit of time I’ll write a follow-up post that explains how to do it. Having an icon you could click after selecting a table or block of text would be a lot quicker and easier than running find/replace repeatedly.


      • As a quick suggestion, you need to:

        (i) Start the macro recorder. If on Office 2007 or later, you need to have the Developer Tab added to the Ribbon. You can do this from “Office Start Icon” (circle at top left) > Word Options

        (ii) Record any old macro – click a few buttons then stop macro recording

        (iii) Click Alt +F11 to open the VBA user interface. Find the “dummy” macro you created and replace the whole of it (everything including the “Sub” and “End Sub” lines) with the final version of the macro code in my post above. Save the changes

        (iv) You can run the new macro from the Developer Tab. Just highlight the block of text you want to convert then run the macro

        (v) If you want, you can add a macro icon to the mini toolbar at the top left. Click down arrow by the toolbar then choose “More commands”, associate an icon with the new macro



Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: