Excel – Format numbers in different locale (with period instead of comma as decimal separator)

microsoft excelmicrosoft-excel-2013

I am using Excel 2013 on a German version of Windows 8.1. When I format a number as a number, in cells or in a diagram, it is displayed with a comma as decimal sign (like "3,1415"). I would like to display the number with a period instead, like in the English (USA) locale.

I tried a number format with an explicit locale like

[$-409]0.0;@

but that didn't work. It does work for dates, though ([$-409]MMM JJ;@).

How can I format numbers with a period? I'm looking for the most non-invasive solution, so changing my system locale is not an option. Changing the language on a workbook or sheet basis would be OK, but I'd prefer something that works on individual cells or diagram elements. I do not want to change the data, so the substitute function etc. won't work.

Best Answer

As a work around: in the advanced settings, you can chose whether or not to use the system separators, and define your own ones.

Related Solutions

Excel – Format a number with optional decimal places in Excel

Alternatively, you can solve the "optional" decimal point problem using the following solution:

Number Format: General;[Red](General)

This will add the decimal place and fractional value accordingly, while formatting negative numbers in a more legible way.

As for the poster's original question, you still need to round/truncate the "extra" decimal points using a formula. However, this is a simple formula of =ROUND(<value>,<desired decimal places>) that is not extremely computationally expensive.

Examples:

2500 -> 2500
0.25 -> 0.25
-2500 -> (2500)
-0.25 -> (0.25)

Excel – With German regional settings, how to display some cells in U.S.-currency-format e.g. $1,000.00

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

Best Answer

Related Solutions

Excel – Format a number with optional decimal places in Excel

Excel – With German regional settings, how to display some cells in U.S.-currency-format e.g. $1,000.00

Related Question