1

I have a weird problem with custom-formatting MS Excel 2010.

I am just trying to make formatting of custom number, for example 110,5 should be shown as 110.5, but there is my problem:

#.# or any other combination like #"."# or 0.0 or #.0 or #"."0 doesn't work at all.

On Office page I found it should be : #.0# but any combination of dot . without " " tags doesn't work and Office keep telling that he can't use this format.

Any idea how to make formatting of any numbers in this format (#.#)?

6
  • 2
    How is your computer configured as to internationalization location? Do you want this format to override the display of the number for other users without regard for their internationalization settings? Dec 10, 2013 at 0:02
  • I dont want to share it with anybody, its just part of my test in school (excel - cells formatting), but when I was trying to use custom formatting i get stuck on those dot problems, just cant use dot instead of ','... Language/location settings: Czech Republic (may this be the problem??) Keyboard: Czech / English US
    – D8tectx
    Dec 10, 2013 at 1:10
  • 1
    Was the test written by someone in the Czech Republic, or was it written elsewhere? The localization could be an important part of this, and knowing if it is part of the test, or a circumstance outside the realm of the test is important. Dec 10, 2013 at 1:49
  • @D8tectx: In Czech Republic comma , is being used as a decimal mark while in English speaking countries dot . is being used for this purpose. This behaviour is influenced by the locale (internationalization) setting of the computer. Dec 10, 2013 at 2:25
  • @Jason Aller - I didnt see the test, this is just part of my materials for the upcoming test so I am trying to learn how to change , to . I think that in my school it will be the same language preset as in my computer. Anyway is there is no posibility to change it through custom formatting, is there any other way how to change , to . ?
    – D8tectx
    Dec 10, 2013 at 2:33

2 Answers 2

5

You cannot use formatting to display a decimal point as a decimal comma or vice versa. These are delimiters used by the operating system. You CAN override the system separators in the Excel Options (File > Options > Advanced > untick "Use system separators" and replace with the separators you would like to use instead.)

-1
=INT(A1)&IF(A1-INT(A1)=0,"", ","&INT((A1-INT(A1))*10+0.5))

try this. Change , to . depending on the way you want it. Remove the .5 for rounding.

You must log in to answer this question.

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