There seems to be something wrong with a certain custom number format. This is the custom entry I made:
It should display any number as I entered it except for 0.



As you can see in column A of my worksheet, when I enter a number with this format the number is treated as text, not a number.

Bug 2: In column B, I entered 0's and converted them to values, but the 0's display even though the format is supposed to suppress them.

What gives? I want a custom format where numbers are displayed as General but not zeros.

Attached Files

Sample.xlsx (8.8 KB, 3 views)

Format both columns as "0;-0;;@"
You can enter 0's, but they aren't displayed.
NB! the value of cell with entered 0 in it remains 0, Formatting cant change the value in cell (date format will be exception, as with it whenever the entered value can be interpreted as date, the Excel itself takes over and replaces the entry with date in current format) - it only affects how this value will be displayed!

Not sure what you mean by NB. the problem with "0;-0;;@" is that if I enter a number as a decimal, it rounds off to the nearest integer. That's why I was using my formula:
"General;-General;;@"
It would seem to display the number as entered except for zeros, but as I said, it seems to create strange problems. I don't understand why my formula does not work

It seems to with without the @ symbol at the end: "General;-General;;"


But then it is blank when I enter text. I don't see why including the @ screws up the format.

I've posted a spreadsheet which shows I can't use "General" in the format without either suppressing Text or not suppressing zeros

Attached Files

Bug.xlsx (9.5 KB, 3 views)

Do you want enter numbers and text into same column? When yes, then reconsider the idea!

Use tables to enter any data - with header row at top, and every table column dedicated for single type of data. In case you need to present entered data in some different format, create report(s) sheets, which read data from data entry sheet(s), and display it in wanted format.