My Windows 7 has regional settings set for German/Germany.

This causes currency values in Excel to appear like this:

234.234,00 €

However, sometimes I want currency data in a cell to appear like this:


No matter how I format the cell (see below), the closest I can get it is to look like this:


How can I get specific cells to format currency with a dollar and comma-as-thousands-marker and period-as-decimal-marker?

enter image description here

Regional Settings:

enter image description here

  • I assume there's no way to do this using Excel (even Excel2010), since the currency format is set in the system settings for every document. But OpenOffice/LibreOffice allows for setting the regional settings per cell. So, if you're not bound to Excel, Openoffice or Libreoffice may worth a try. – tohuwawohu Aug 9 '11 at 13:47

Surely this is solvable with some Excel cell regional setting override somewhere, however, until that is found, if anyone is looking for a pragmatic answer to this, here is a hack I used to reformat numbers in adjacent cells to U.S. current format:

=CONCATENATE("$";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(G28;"###.###.###,00"); ",";"z"); ".";",");"z";"."))

enter image description here


You can create custom number formats in Excel, and can base them on the existing formats rather than start from scratch.

If you select one of the cells that appears as close as you can get it and choose, from the cell format list shown above where "Accounting" is selected, the "custom" entry that appears at the bottom of the list, you can edit the format code that Excel uses.

All you need to do is to swap arounf where "." and "," appear in the format code and you are away.

I haven't used them very much for years and you shouldn't really need to know the details for a minor change but to get you on your way if you want to do anything fancy here's what I remember about the codes (which may not be correct but you'll get the flavour): there are four parts to the format code; what appears when the number is positive, negative, zero and text. "_" shows a space of the width of the following character, "*" instroduces a fill of the following character, "#" stands for any digit etc

  • sorry, doesn't work. The comma is still treated as decimal separator. Excel doesn't accept a dot anywhere after the comma even when trying to define a custom format. – tohuwawohu Aug 9 '11 at 14:08

Customized Currency setting / changing currency format : The aboe options are brilliant. But if you would like to format the currency in the same cell where you have the number is, probably in Excel 2010 > format cells ...in the category you would see 'Custom'on the bottom. Click one of those and replace that with the below :

([$MSEK-2]* #.##0,00);([$MSEK-2]* (#.##0,00);([$MSEK-2]* "-"??_)

I have used MSEK (Million Swedish Krona) for conversion, but you can replace that with any other text you would like to.

Hope this will help.

Keep sharing knowledge, it grows :)o



Here is visual basic macro that I wrote for Excel 2016 on May 2016.


Feel free to update it, but make sure you change the code for your desired cell output.

  • Link-only answers are not considered very useful in this Q&A website. Can you include the essential elements in the body of the answer? – RedGrittyBrick May 12 '16 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.