0

I try to use the number format #.# to display 7 as 7, but it does display "7." (in Excel, or other MS environment)

I want 7.1 to be 7.1, and 7 to be 7, and 7.49 to be 7.5, that is #.# (see here)

I can't use formulas in that cell, because user will edit it, I can only work with custom formatting of the cell.

enter image description here

Is there a way to display a number without insignificant zeroes, but eventually with one decimal place if there is something after the decimal separator?

As the format #.### means, 7 for 7, in that case Excel does not render the value properly, this should be considered a bug, and fixed by Microsoft. Here bellow a .Net sample with the #.# number format displaying correctly 123 as 123 (without the dot). https://dotnetfiddle.net/JDBLM2

My question is because I try to format in a Excel-like (telerik) spreadsheet, numbers, something like

sheet.range("C11:Z100").format('[>=1]#;[<1]#.####');

but there is not a such formatting condition for integers, like:

sheet.range("C11:Z100").format('[=INT(C11)=C11]#;[=INT(C11)<>C11]#.####');

I can't use VBA, because I have the spreadsheet only, I work on a web application containing spreadsheet, and I have no VBA, I have only C# server side code, and JavaScript on the client side.

2
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – DavidPostill
    May 1, 2021 at 14:22
  • Comments have been moved to chat. Please continue the discussion there as asked. New comments will be deleted.
    – DavidPostill
    May 1, 2021 at 14:29

1 Answer 1

0

You cannot do what you want without combining both types of formatting that Excel offers, or using VBA.

VBA would be a simple "chunk your way through all the cells" fixing the formatting kind of approach. If you're up to it, go for it. If not, I am not the fellow who can help out with details.

However, the traditional means to solve this is to format the cells using regular formatting to the basic format you desire: #.#. So far, so good. You have the "7.1"... now to rid yourself of the "7." entries.

Apply a Conditional Format using a rule testing an entry for "integer-ness" and applying "General" formatting if the test passes. So all the "7.1" entries are unchanged while the integers are reformatted to "7" and you are done.

This has the advantage over VBA of being done automatically and on-the-fly as you enter values. Does the CF applied add a burden to your spreadsheet? Oh yeah... though you shouldn't notice it ever nowadays unless you have a truly humungous data set. And I'd point out the VBA would neither come compute-free(!!!) nor would you be happy automating it and seeing that hit every keystroke. So...

Be nice if it could be natural, but... Excel ain't havin' none of that.

Downside of using CF is any structural change to your spreadsheet that touches the range you specified will probably Balkanize the range which you will need to inspect and repair.

(By "Balkanize" I mean split up into tiny parts, not feel national pride inadequacies that come from being utterly dominated by outsiders for thousands of years, and feel your only way to puff yourself up and pretend you're men amongst men is to murder men, women, and especially their children by the thousands from the other peoples nearby, and amongst you, who have had the same sad utterly dominated history you had. No, not the modern sense of "Balkanize"... the older, less horrible sense.)

(For decades this has been the solution. Excel offers nothing to help out and makes no apologies for it.)

3
  • I'm so curious why you create a new user every time you answer a question.
    – Alex M
    May 14, 2021 at 23:27
  • The format #.### means, 7 for 7, here Excel does not render the value properly, this should be considered a bug, and fixed by Microsoft.
    – serge
    May 17, 2021 at 7:40
  • I can't use VBA, because I have the spreadsheet only, I work on a web application containing spreadsheet, and I have no VBA, I have only C# server side code, and JavaScript on the client side.
    – serge
    May 17, 2021 at 7:43

You must log in to answer this question.

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