Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

enter image description here

Regional Settings:

enter image description here

share|improve this question
    
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

3 Answers 3

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.

share|improve this answer
up vote 1 down vote accepted

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

share|improve this answer

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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