Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
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:
$234,234.00
No matter how I format the cell (see below), the closest I can get it is to look like this:
$234.234,00
How can I get specific cells to format currency with a dollar and comma-as-thousands-marker and period-as-decimal-marker?
Regional Settings:
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";"."))
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
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
Pranish.
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.
