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.

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.

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 at 16:26
1  
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 at 16:30

1 Answer 1

If you have a cell that displays pi as:

3,1415

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:

3.1415
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 at 17:41
1  
@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 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 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 at 17:58

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.