[Solved] Bracket negative numbers

[richardm]

How do you put brackets around negative numbers? I prefer to not use the minus sign.

[acknak]

I assume you're using Calc--right?

Anyway, Format > Cell > Numbers > Category: Numbers... Hmm, I thought there already some built-in formats that displayed negative numbers in brackets, but I can't find any now.

Oh well, you'll have to make your own. Something like: 0;(0) or #,###.00;(#,###.00) will work. See the online help under "format codes" for all the details.

[pingju]

Format code #,###.00;(-#,###.00) gives you 1,234.56 and (-1,234.56)

If you want to get rid of the minus sign "-" from (-1,234.56), I do not think that is a easy task.

Format code #,###.00;[-#,###.00] gives you nothing special because "[]" is used by the system for limiter.

[Cambirder]

If you want to get rid of the minus sign "-" from (-1,234.56), I do not think that is a easy task.

Its actually as easy as acknak suggested.

If you want to display as integers use 0;(0), to one decimal place 0.0;(0.0), 2 decimal places 0.00;(0.00) ect.

If you want to show as currency then $0.00;($0.00) and £0.00;(£0.00) also works

[pingju]

Ya, that is so easy. I did not test with that. Thanks.

[keme]

One more detail for good looks:

If you format a column of numbers like this, you want to have the digits of positive and negative numbers to be aligned. The underscore+character inserts spacing into a format. To insert a space the width of a parenthesis, use _).

The full format specifier would then be #,###.00_);(#,###.00)

[acknak]

The underscore+character inserts spacing into a format.

Interesting--I did not know that. Thanks, keme!

[Cambirder]

As everyone seems to be learning something from this, is there a difference between using 0.00;(0.00) and #,###.00;(#,###.00) or is the second option just a more long winded way of achieving exactly the same thing?

[acknak]

The longer version will include thousands' separators where appropriate:

12345.677 --> 12345.68 vs. 12,345.68

The actual separator character will vary depending on the locale.

[keme]

The # format specifier instructs to print "significant digits", so leading zeroes before the decimal point and trailing zeroes after will be suppressed. Using zero as format specifier instructs to print a digit always.

So the longer format will display 0.5 as .50, while the short will display the zero before the point: 0.50

[Vasuah]

Better than underscore ( _ ) is the hard space character which is entered with CTRL-Shift-Spacebar all together on any English keyboards.

[keme]

Better than underscore ( _ ) is the hard space character which is entered with CTRL-Shift-Spacebar all together on any English keyboards.

No, it's not.

1. I don't think you can use that "hard space" at all in Calc.
2. In any case, the format code entry field will not accept it. If it would (as may be the case with third party builds of the suite), and Calc is set to use spaces for thousands separator (as is the case for some locales), each trailing space would probably divide the cell value by 1000 before displaying, like it does with trailing normal spaces.
3. A space has a defined width. The underscore+character makes a space with the width of that other character, iow. neither a normal width space, underscore, nor underscore-wide space.

[MrProgrammer]

If richardm really wants brackets [] and not parentheses (), they can be escaped with a backslash in the format code so they aren't interpreted as a "conditional bracket" expression. For example: #,###.00_];\[#,###.00\]

[keme]

If richardm really wants brackets [] and not parentheses (), they can be escaped with a backslash in the format code so they aren't interpreted as a "conditional bracket" expression. For example: #,###.00_];\[#,###.00\]

That is absolutely correct, but may still be misleading...

---Language lesson---
In the US, using the single word "brackets" (no specifier) usually refers to those square brackets you mentioned.
With common UK English usage, "brackets" usually refers to "round brackets" (parentheses).
---End of lesson---

AFAIK, the common way to display "bracketed negatives" is with parentheses. I've never seen any other bracketing in use for that purpose (but then again, I'm not an accountant).
Also, richardm has not posted anything after that single question, well over a year ago, so I guess he's satisfied ...

[jrkrideau]

If richardm really wants brackets [] and not parentheses (), they can be escaped with a backslash in the format code so they aren't interpreted as a "conditional bracket" expression. For example: #,###.00_];\[#,###.00\]

That is absolutely correct, but may still be misleading...

---Language lesson---
In the US, using the single word "brackets" (no specifier) usually refers to those square brackets you mentioned.
With common UK English usage, "brackets" usually refers to "round brackets" (parentheses).
---End of lesson---

And as a Canadian I'd normally say brackets () and square brackets [] though I would talk about something in parenthesis

Lovely language English

AFAIK, the common way to display "bracketed negatives" is with parentheses. I've never seen any other bracketing in use for that purpose (but then again, I'm not an accountant).
Also, richardm has not posted anything after that single question, well over a year ago, so I guess he's satisfied ...

[regine]

Hi !
thanks for ur help, this is what i did for negative numbers with parantheses, a comma for the the thousands and no decimals

#,##0;(#,##0)

it gives me (50,000)

thanks again
reg.