[Solved] Format Cell Number with (#,###) or (#,###+)

[Standinghawk]

I am working on a Calc Spreadsheet and one of my columns needs to represent a number in parentheses with or without a plus sign after the number. (ie (1,000), (500+), etc) I would like to just enter the number without having to enter the parentheses because all of the numbers will have them. The plus sign on the other hand I would like to just enter the number and the plus. (ie. 1000, 500+, etc) Is there a way to format the cells under Format, Cell, Number? Something simple. I would rather not use a macro. Below is a picture of an example that I am trying to work with. The second column I have working fine, It's just the format of the first column that I am having trouble with.

20211015_210633.gif (3.55 KiB) Viewed 3360 times

Thank you in advance.

[RusselB]

How is Calc to know if the number should be formatted as (#,###) or (#,###+)? aka: what is the difference between the two formats in regards to the numbers entered?

[keme]

The challenge here is that in order to apply number formatting (like thousands separators) to input in a spreadsheet cell, the input must represent a number. Not "a number and something".

• 123 is a number
  123+ , as you are using it, is not a number. It is a number with some additional info.
  Techically, in Calc the + is taken as a postfix number sign, indicating "positive number", so 123+ is not different from 123 and you cannot have formatting which differentiates the two inputs.
  Howver, this means that Calc accepts prefix or postfix number sign, which you can use for a cheat. See below.

The closest you can get by simple formatting is to force text interpretation. With that you can have the parentheses added, but you must type the thousands separators. Format code (@)

If you are willing to cheat a little, you can get more. Calc allows three separate number format codes. If you use all three, they are by default applied to positive numbers, negative numbers and zero, respectively.

Try format code (#,###);(#,###+);(#). This will append a + to negative values. You will then need to enter a - instead of a + to have your number display with a +, and if you want to calculate on the numbers you need to remember that some are negatives. Also, if some of your numbers are negative, this approach does not work.

If the text entry with thousands separators is too cumbersome and you can't use the negative numbers cheat, there are two other possibilities:

• Separate input cell and display cell. Use forced text input and parse by text functions to create desired output.
• Macro

Post back if you need help with that.

[Standinghawk]

Thank you keme, that is exactly what I was looking for.

There are no negative numbers, and they will not be used in any calculations.

I tried it and it works... except... in testing it out I noticed some of the cells will also need to represent (800-1,000). Lucky, they won't need to represent (800-1,000+) You don't have a miracle hidden up your sleeve for that also do you? Maybe that's asking too much from the program.

Thanks for the help you have given me, and hoping for a miracle.

If there is no miracle, I will change post to answered.

[Hagar Delest]

How is Calc to know if the number should be formatted as (#,###) or (#,###+)? aka: what is the difference between the two formats in regards to the numbers entered?

I agree that as long as we don't know what is the purpose, then it will be difficult to provide the most appropriate solution.

I would associate the desired number format to a cell style and the apply the styles with a shortcut (then no need to use a trick like the negative formatting).

I tried it and it works... except... in testing it out I noticed some of the cells will also need to represent (800-1,000). Lucky, they won't need to represent (800-1,000+)

This is definitively a text string. You can have (Format Cells > Numbers tab) the Category set to Text and in the Format code field add (@).

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the *EDIT button if your issue has been fixed.