Add thousandths separator to numbers, right of decimal point

[gnuarm2]

I’m sorry. You keep talking about “standards”. I’m trying to get work done, the same way I’ve done work for some 50 years. You are telling me this software has lost capabilities, because it is mandated by a standard???

I have no idea what you are trying to say with “No way”.

Ok, what is that format code? I keep hearing that it is not available because of a standard. So what is the format code I need?

How does the link you provide have anything to do with the commas I wish to insert to mark off the 1,000’s positions?

It’s like we are not having the same conversation.

[gnuarm2]

I think I need to throw in the towel and accept that people here, literally can’t understand what I’m talking about… or are somehow saying that what I want to do is invalid and I should simply not do it. What I want is just wrong!

That sure makes programming a lot easier.

I ran into something similar in a PDF reader once. They supported highlighting, of course, but not word based. If you double clicked to select a word, you could not move the cursor to select any further text. On trying to find out if they would consider fixing this limitation (the “fix” being common in nearly every computer program I’ve used since the DOS era) I was told that Adobe Acrobat didn’t do it, so it would be “wrong” for any other PDF viewer to do it. That’s just insane.

[mikekaganski]

@gnuarm2

people here, literally can’t understand what I’m talking about… or are somehow saying that what I want to do is invalid and I should simply not do it. What I want is just wrong!

Did you even read my comment?

@mikekaganski

I believe, that group separation is not limited to whole part only by any standard

@mikekaganski

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.

Sigh. You seem to read what you want to read, not what you are actually told… shrug.

[gnuarm2]

Thank everyone for their assistance.

[keme1]

@gnuarm2

I need to use a comma to separate thousandths (not just thousands) in displayed numbers.

A workaround to achieve this for display

Assuming that the number is in A1, a “display version” of the number as text can be achieved by the following fomula:

=IF(A1<0,"-","")&TEXT(INT(ABS(A1)),"#,##0")&REGEX(REGEX(TEXT(MOD(ABS(A1),1),".#################"),"([:digit:]{3})","$1\,","g"),"\,$","")

some explanation

• The initial IF() and the instances of ABS() are together required if you need to handle negative numbers. The MOD() function is correct and predictable in its handling of negatives, but not always intuitive
• The integer part is easily formatted as desired.
• The fraction part extracted by “modulo 1” calculation and preformatted without any leading zero.
• The inner REGEX() inserts comma to the fraction part after every 3 digits.
• The outer REGEX() removes a trailing comma which will otherwise occur when decimal count is a multiple of 3.

This was the simplest I could make it while catering for negative numbers, large numbers and an arbitrary number of decimals. (Just add # characters to allow for more decimals.)

Please note:

• The output from this formula is text, which is by default aligned left. Use cell formatting (style) for proper alignment.
• As already determined, this number formatting layout is not supported by Calc, so the output is not reliably interpreted by the VALUE() function. Use the source value for further calculations. This workaround only serves display purposes.