Changing Currency Formatting for a Single Workbook

by Allen Wyatt
(last updated August 27, 2018)

2

George has a single workbook that he needs to submit information using a Latin America currency format like this: 238.769,76. Short of changing his global settings George has not been able to come up with a way to handle this. He wonders if there is a way to change the currency format for only a single workbook.

The "global settings" of which George speaks are technically referred to by Microsoft as "regional settings." These are, indeed, handled by Windows directly, so any change there affects all programs operating within Windows, including Excel.

There is a way to temporarily change the settings by using a VBA macro to do the magic. If you tie this macro to the opening and closing of a particular workbook, then your needs might be met adequately. Here are the macros:

Dim oldDecimalSep As String
Dim oldThousandsSep As String
 
Private Sub Workbook_Open()
    oldDecimalSep = Application.International(xlDecimalSeparator)
    oldThousandsSep = Application.International(xlThousandsSeparator)
    Application.DecimalSeparator = ","
    Application.ThousandsSeparator = "."
    Application.UseSystemSeparators = False
End Sub
Private Sub Workbook_ BeforeClose(Cancel As Boolean)
    Application.DecimalSeparator = oldDecimalSep
    Application.ThousandsSeparator = oldThousandsSep
    Application.UseSystemSeparators = False
End Sub

To use these macros, open the workbook you want to use the differing format separators. Then, open the Visual Basic Editor and double-click the ThisWorkbook object in the Project window. Put the macro code into the code window and save everything.

Now, whenever you open the workbook, Excel stores the existing separator information and changes it to use commas and periods as George wants. When the workbook is closed, the old separator information is restored.

There is one gottcha with this code: While the workbook is open, any other workbook also open will use the different formatting. The trick, then, is to only work with the "special" workbook alone, knowing that when you close it everything will return to normal.

There is one thing to remember when using code like this: You are only changing how the numbers you see are formatted. You may not even need to do it, because of the way that Windows handles regional settings. In other words, you might see a number appear like this in Excel: 1,234,567.89. When you send that workbook to a user in a country that uses different regional settings, she would see it like this: 1.234.567,89. In other words, Windows takes care of displaying the numbers correctly for the region of the world in which they are displayed.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13307) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using the Discussion Pane

Tips for using the Discussion pane during a Discussion session.

Discover More

Getting Rid of Blank Labels in a Filtered Merge

If you are filtering a mail merge in Excel, and you get blank labels in the printout in Word, chances are good that ...

Discover More

Viewing Your Work Full-Screen

Want to use the maximum space possible for displaying information on screen? You'll want to learn how to use the ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Formatted Dates Appear Differently on Different Systems

When you format a date in a specific manner, you may be surprised to see that the format changes when you open the ...

Discover More

Matching Formatting when Concatenating

Convert a numeric value to text and you may be surprised by how Excel displays the value. Here's a run-down on exactly ...

Discover More

Pasting Numeric Values in Other Programs

When you paste information from Excel into other programs, you may get more than you actually want. It is not unusual for ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 2?

2014-11-19 02:25:01

Rudra Sharma

@ Jose
For date formula would suffice. Just use text function.


2014-11-15 06:26:18

Jose Lourenco

Great tip.
I have frequent fights with my guys, because they insit on using different formats.
I assume one could also write a similar Macro for date formats.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.