MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Formatting Currency based on the Locales

  Asked By: Sean    Date: Oct 03    Category: MS Office    Views: 1705

How can i format the Currency value in the Excel cell with the Comma
separator based on the Locales used.
Is i use the function TEXT(), wrong values are displayed for few
country currencies.
Please suggest me with any other method which can be used to format the



5 Answers Found

Answer #1    Answered By: Verner Fischer     Answered On: Oct 03

I am not quite sure what you want. Do you want to set up a spreadsheet which
will format currency  as Rands if it is used in South Africa, Yen if it is used
in Japan and Bahts if it is used in Thailand


Do you want on one spreadsheet to have some Rands, Some Yen, Some Bahts etc
and each to be formatted according to the country it relates to?

Answer #2    Answered By: Luz Hayes     Answered On: Oct 03

I want to set up a spreadsheet which will format currency  as Rands
if it is used in South Africa, Yen if it is used in Japan and Bahts
if it is used in Thailand.

1000'th sperator in few countries are not "," and i have to take care
of this also. display of currency symbol like '$' or 'euro symbol' is
not very necessary.

Answer #3    Answered By: Vidos Fischer     Answered On: Oct 03

This should happen automatically. If you use the Currency format, it will
pick up the appropriate one from the Regional Settings in Windows.

Answer #4    Answered By: Hoor Khan     Answered On: Oct 03

What I have understood, that currency  should be fromatted on the basis of the
country. meaning If the same sheet being open in India the currency would be
formatted to RS, If it is US it would be $ like this.
If this is what I understood, we need to write a VBA code using this Lib
GetLocaleInfo Lib "kernel32", which will return you the country
GetCountryName = GetUserLocaleInfo(LCID, LOCALE_SISO3166CTRYNAME)
Now using the SELECT...CASE you can change the global setting of the currency
irrespective to country.

Answer #5    Answered By: Hugo Williams     Answered On: Oct 03

Recently I was in a friend's place discussing problem on excel  spread
sheet and this friend posting a challenge that if I cannot create an
icon button for formatting  "Ringgit Malaysia" I will have to spent a
night away from home. I figured a way of doing it within 10 minutes
and never asked any favor in return.

(1)First I open a blank Excel Workbook, Book1.

(2)Insert the number 123456789.12 in Cell A1.

(3)Click Tools/Macro/Record New Macro and click OK.

(4)Click Format/Cells/Custom/, under the "Type" prompt key
in "RM"#,##0.00. You will notice RM123,456,789.12 displayed  on the
sample column above. Of course you can choose other format  or other
currency symbols if you wish. Then Click OK.

(5)Click Tools/Macro/Stop Recording. Macro1 will be created.

(6)Ensure the Formatting Tool Bar is shown on top, if not click
View/Toolbars/Formatting and the Formatting toolbar will appear on top
with the `$' currency  Style icon.

(7)Click Tools/Customize to pull out the Customize prompt.

(8)Choose Format Categories and scroll Commands until `$' Currency
Style is selected. Click and hold the mouse and drag the selected `$'
selected icon beside the $' Currency Style on the Formatting Toolbar,
i.e. creating a duplicate `$' icon under the Formatting Toolbar.

(9)Click Modified Commands under the Customize Prompt. You can
* Change the Name: to Rinngit Malaysia or your preferred name.
* Assign the Macro1 to this currency style.
* Edit Button Image to suit your taste.

(10)Close the Customize Prompt and you have created a currency style
of your taste.

(11)This extra Currency Style icon will remain in your Excel set up
until you have removed it.

Didn't find what you were looking for? Find more on Formatting Currency based on the Locales Or get search suggestion and latest updates.