3

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.

  • 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
  • 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 '14 at 16:30
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
  • 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
  • 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 '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
1

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

  • ​​‌‍H​​‌‍o​​‌‍w​​‌‍?​​‌‍ – Mr Lister Apr 5 '18 at 12:58
0

I have the same issue: the number is in format "-34,754.27" and I want it to be translated to a number, in the english locale, so "-34754,27" or "-34.754,27"

I am using the build it NUMBERVALUE function:

=NUMBERVALUE(TEXT,DECIMAL_SEPARATOR,GROUP_SEPARATOR)

This will return the number in the current locale, by interpreting the text according to the separators you're providing.

Alternatively, you can go the brute force route, and do the search and replace of these separators yourself - the issue with this approach, besides not being elegant, is that you'll end up with a text that you need to further translate to number and you will get back to NUMBERVALUE so better use that instead and alone:

(the test in this example is [@Suma] )

=REPLACE(IFERROR(REPLACE([@Suma],FIND(".",[@Suma],1),1,""),[@Suma]),FIND(",",IFERROR(REPLACE([@Suma],FIND(".",[@Suma],1),1,""),[@Suma]),1),1,".")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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