Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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.

share|improve this question
to clarify: your goal is to have it display the numbers using US locale on your German machine, right? Because i suspect the locale would be the right one, if someone opened your file on a US machine. – TheUser1024 Mar 10 '14 at 16:26
Exactly, I want the numbers to display with a period on my German machine, since I want to write an English document. (The readers of the document don't know or care what machine I'm using, they will just complain that the decimal separators are wrong.) The numbers display correctly on English (American) systems. – jdm Mar 10 '14 at 16:30

If you have a cell that displays pi as:


Select that cell and run this small macro:

Sub Reformat()
    Dim st As String
    st = ActiveCell.Text
    st = Chr(34) & Replace(st, ",", ".") & Chr(34)
    ActiveCell.NumberFormat = st & ";;;"
End Sub

The display will become:

share|improve this answer
I believe your solution will have all kinds of effects on the depending formulas. The main issue here is the collision of decimal and thousands seperators, the OP wants them swapped as a "number format" so it does not affect the underlying value. – TheUser1024 Mar 10 '14 at 17:41
@TheUser1024: My little macro should not change the value of the cell, only how it is displayed or printed.......clearly this approach can only be used on constants and not on the results of formulas. – Gary's Student Mar 10 '14 at 17:46
Okay, interesting. Neat trick! Well it depends, if you run it for a selection of cells on every recalculate (maybe not the best of ideas?), it should work for a broader scope of cells that your simple version. To clarify: To a cell with the number 2,5 in it, this macro applies the format "2.5";;; (with quotes). So it will remain to say 2.5 no matter what value you later put in there. – TheUser1024 Mar 10 '14 at 17:53
@TheUser1024.............If you want to change the value in the cell......... first clear the cell's format.....then enter the new value.......finally re-apply the macro! – Gary's Student Mar 10 '14 at 17:58

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .