Numbers: leading zeros in text

Thanks Badunit.

The source of the file is a php program accessing a data base and outputting a text file. I'll inspect.

However the problem crops up even after passing the file through BBEdit. I'll do some more experiments.

The real bad thing is the leading zeroes. Your sarcasm is appreciated. Apple can be assured that my next computer will run Linux or Windows.

The column in question has a mix of postal codes from many different countries, some using letters, so your suggestion will not help.

Fortunately the data did not contain the character "|". So I first changed all "0" to "|", then pasted it into Numbers, then changed the "|" back to "0" and that worked. Sigh. As I often say: "before we reach the mountains of Artificial Intelligence, we mus cross the desert of the halfwitted devices."

As an aside (mentioned elsewhere) I use plain numbers for dates. Today is 20211107. YYYYMMDD. But I use a custom format to display them: ####-##-##, specifically setting the type to number. Thus 20211107 should be displayed as 2021-11-07 but be a number. When I compare 20211107 with 20210101, Numbers tells me I'm comparing a date with a number, because in one case it uses the displayed value (which of course is always really a string) which it then for a mysterious reason interprets as a date (I never use date formats anywhere ever). Sigh.

:-)

I know what you mean. The main thing that made me stick to Macs since 1984(!) was the coherence of the interface and the existence of GUI guidlines. But in Big Sur that coherence is gone. This is not the place to make a list, but it is long and amazing. Just one example: when you do not want to save a document you've just changed, depending on the application, you now have to press "Don't save", "Revert changes" or "Delete". Confusing, and the only really valid answer is "Don't save".

The current situation comes from an irresistible urge to change things, even when they are perfectly satisfactory. But if it ain't broke, don't fix it.

Your solution of using anoter table full of formulae to fix the problems had also occurred to me. The formulae would be horrendous.

Excel runs on Macs, but my version is 32bit so no longer works, and I hate to buy a new pack, just for one or two sheets I need to send out. And certainly I do not want subscriptions. I never use Word or PowerPoint.

As to the dates: it is a little more subtle than described.

This formula:

SUMIFS(B,A,">"&C2)

sums the values of the B column when the number in the A column is greater than that in cell C2.

It works OK if A and C contain numbers with automatic format. It fails (but not with an error) if the numbers in column A and the number in cell C2 are formatted as ####-##-## (while still being set to type number).

As an additional little bit of info: in my System Preferences the date format is set to custom with ####-##-##. But note that NONE of my spreadsheets ever used type date in any cell. And that it all worked fine in Numbers 2.x Also when I use the VALUE() function around the comparison cell reference, it works.

But, in the end, those behaviours are bugs. I've reported them and they should be fixed. Or rather should simply not have shown up.

The slogan now seems to be form before function. The first sentence of Apple's description of Numbers starts:

"Create gorgeous spreadsheets with Numbers..."

I don't want them to be gorgeous, I want them to work.

Have a nice day, and thanks for helping me blow off steam.

Badunit, I can't attach a Numbers file, but made a screenshot:

The cells in columns A and B all contain numbers. So do C1 and C2. Their format is ####-##-## but the type is number.

C8 is SUM(A9:A19) and produces correctly 132.

C3 contains

SUMIFS(B,A,">"&VALUE(C$1),A,"<"&VALUE(C$2))

which also works correctly, but if I make it into

SUMIFS(B,A,">"&C$1,A,"<"&C$2)

it tells me that the formula compares a date to a number, and displays 0.

"try it, you'll like it!"

:-)

I think much of the confusion is historical. Many applications of personal computers were made by people with no formal training in informatics. (see at end) They just started writing code at the top left and down to the end.

It is therefore not surprising that Excel makes the same errors, and so they creep into anything that needs to be compatible.

A well-designed spreadsheet should for each cell distinguish between

the formula (if there is one),

the value,

the type of the value,

the presentation or display of the value (which is always a character string),

the limitations on editing the formula or value.

Formulae should always use the value and its type. Thus my cells have a value that is a number, a type that is a number, but a presentation that does not look like a number. In editing a value, Numbers does revert to showing me a number, i.e. I cannot edit the hyphens.

The SUMIFS should use the value, but they do not. The comparison operator ">" should verify that both sides have the same type. In this case they are of the same type, therefore the implementation of SUMIFS is incorrect. The bug is not present in Numbers 2.x

Further, if a cell refers to one of these number cells with a formula like

= LEFT(A5,3)

that should be reported as an error because the formula tries to get the first three characters of a character string, but A5 contains a number. Something like

=LEFT(DISPLAY(A5),3)

would be acceptable, because it would explicitly refer to the displayed character string. Or, to keep things simple, one might admit implicit use of the display string in certain formulae like LEFT, MID, RIGHT etc. and that should be mentioned in the manual.

But most certainly I should not have to use VALUE inside the SUMIFS.

Robert.

Ignorance that persist:

C-type programmers use "to cast" as "to convert". But the meaning is very different: If I convert an integer to a floating point number, the value is the same but the internal representation changes. If I cast a variable to another type, the internal representation stays the same, a string of bits in memory, but it will be interpreted as of a different type.

Resolution and definition: the difference was first ignored, then it was realised there were actually two different measures, and now they are pixel density (resolution) and resolution (definition).

And so on. Not to speak of syntax.