Add thousandths separator to numbers, right of decimal point

English
,
#1

I need to use a comma to separate thousandths (not just thousands) in displayed numbers. I can’t find a way to do that. Typing a comma into the appropriate place in the number format string (right of decimal point digits) does nothing, other than making it a “user-defined” number. Even then, the comma separator does not show and the format string is not saved.

One of the format strings I’ve tried is “#,##0.000,00” (without the quotes, of course). My defaults are set for “.” as decimal point and “,” as thousands separator. The number being formatted is the result of a formula.

Any info I didn’t provide?

#3

Just a bit of additional information:

  • The majority of locales (language or region oriented settings) use the comma as the decimal separator.
  • The others (if based on “Western” scripts) use the point in this role.
  • There are exactly two characters explicitly deprecated as group separators therefore: the comma and the point.
  • The responsible institution (ISO) suggests to use a small space (e.g. U+0020) if group separators shall be used at all.
  • A "format" where one of the deprecated group separators may occur before but as well after the actual decimal separator as a group separator is a way to hell in communication.
#4

As a visual workaround, use the SINGLE LOW-9 QUOTATION MARK (low single comma quotation mark) U+201A “‚”. Beware that the symbol meaning is other.

Better with a small space (as pointed by @Lupp). Can copy from between the Xs:

U+2004 x x  three-per-em space
U+2005 x x  four-per-em space | mid space
U+2006 x x  six-per-em space
U+2008 x x  punctuation space
U+2009 x x  thin space
U+200A x x  hair space
U+202F x x  narrow no-break space
U+205F x x  medium mathematical space

Screenshot:
imagen

#7

There are exactly two characters explicitly deprecated as group separators therefore: the comma and the point.

You can deprecate anything you wish. The world will continue to spin as it always does.

This is one of my problems with some software. The authors don’t wish to address the problems users have, so they expect users to redefine how they work.

In any event, I don’t see how any of this has to do with the question I asked. What am I missing?

#8

I also deprecate the mentioned characters in the mentioned role, but this isn’t what I was talking about. There must be standards. Otherwise you never can know what the sender actually meant when you receive a message, and the sender can’t be sure the receiver will understand correctly. Concerning many kinds of data, among them ordinary numbers, the relevant authority is the ISO ( International Organization for Standardization). Neither ISO nor myself will worry about the ways you communicate numbers when writing to aunt Claire. But frequently very much, including complete missions and human lives, can depend on unmistakably communicated information.

You sure, this isn’t just nonsense?

#12

Could you please share how did you learn what authors do, and how they feel about addressing the problems users have? Thank you.

#16

By reading this thread, obviously. Your comment is very much troll like.

#17

But where did you read a single comment from a developer (“author”)? (Until I jumped in)

#18

Once more

In your question you are talking about “#,##0.000,00”, five digits after the decimal separator. How many digits do you really need after the dot?

#20

What if this questioner simply didn’t understand that positions at the right end of a decimal representation of a “spreadsheet number” always are afflicted by rounding and/or the intended resolution and/or the technically (IEEE 754) available resolution?

#21

@Lupp might be; but they still feel OK to draw conclusions from something they read, about some other people committing wrongdoings… and then calling yet some other people “troll like” … surely @gnuarm2 can’t be wrong in all that!

#22

What will be the maximum number of characters in this number, and the maximum number of integers?

#24

Not relevant.

#26

Hello @gnuarm2, yes it is relevant, Calc ( and Excel ) support up to 15 numeric characters for a numerical field, if it has more than 15 characters, it has to work as text.

#2

Please try “backslash-comma” instead “comma”:

image
image352×580 26 KB

2 Likes
#6

Thanks. That gets the first comma inserted, but does not continue to insert commas every three digits, like the comma on the left does.

Is this simply not supported?

#9

Sorry, I don’t understand - there are three commas in my screenshot… Using this formatting technique, you can display your number however you like. You can even use a format code like
#,##0.000" first period "000" second period "000" and other "000
and get from the same 10000*PI() a cell like
31,415.926 first period 535 second period 897 and other 900
It will be the same number, it just looks ridiculous enough to confuse everyone - “sOrRY fOr THe uNeVEn hANDwrItiNg”

Who cares? Indeed, if I answer “This is supported by all spreadsheets” or “No, it is not supported because it violates the standard” - what difference does that make?

#10

When you add a comma three digits to the left of the decimal mark, that spacing is repeated and a comma will be inserted every three decimal places further left.

Your example explicitly puts commas in two different positions right of the decimal mark. After the two places you have individually specified, no more commas will be inserted, no? I find it odd that the same convention is not used on both sides of the decimal mark.

I’m looking for a way to extend the “comma every three digits” to both the right and left of the decimal mark.

When I ask about this not being supported, I am expressing my surprise that a format, which is very common in many places, is not accommodated by LibreOffice. But, I suppose, given the responses I am receiving here in this question, and in other questions, My ideas of common conventions is not perceived as “common” by others.

#11

Yes, because that’s the standard.

Yes, because it’s standard too

No way

You have this way. How many digits are you going to display out of about fifteen available? Generate the format code once that suits your needs, include it in a custom cell style, and apply it wherever you need it.

Where else have you seen such a record of decimal fractions?
A decimal representation of a non-negative real number r is its expression as a sequence of symbols consisting of decimal digits traditionally written with a single separator

#13

I believe, that group separation is not limited to whole part only by any standard; the referenced Wikipedia article shows a sample of Pi and e with decimals grouped in fives. Moreover, there is a citation there in the article:

The International Bureau of Weights and Measures states that “when there are only four digits before or after the decimal marker, it is customary not to use a space to isolate a single digit”.

Note that “or after” that I emphasized in the citation.

It simply isn’t implemented in Calc; but aside from incompatibility of such feature with previous versions, it seems to not be something impossible / outright wrong.

The only problem with manually inserted fixed-position separators suggested by @JohnSUN could be together with the # optional digit mark, like in 0,###\ ###\ ###\ ### format string. Using those, the fixed separators would still appear in the output, making right-aligned numbers misalign … but then, a columns of different-width numbers with different decimals numbers would look awful even without the extra misalignment - so not much of a problem. If aligned to a decimal separator (using ? marks to have spaces instead of trailing zeroes, and using a monospace font), there would be no such misalignment problem… so my advise would be: just follow @JohnSUN’s advise, create a fixed format, and apply where needed.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.