Bug / Number Stored as Text Issue

Question

There is a bug in Excel in which cells are numbers, have been set as numbers, show as numbers when cell formatting tool is opened. But excel is keeping those values as a 'number stored as text'.

The cells in question are the list of values, and I have a total in the bottom right corner. Which incorrectly shows the sum as zero.

Here's the formula for the cell in the bottom right...

=SUM(CC108:CC115)

Here's the Format Cells tool for the cells with values...

But irrespective of these clear settings. There is an exclamation mark next to the cells which shows 'Number Stored as Text'.

That is terrible behavior.

Answer 1

Hello Gavin.Williams,

Thank you for coming to the forum.

For us to conclude that this is a bug, I need you to try this set of numbers (8 of them) on another sheet, when do it you do not need to set the cell format to numbers?

Also, the cell content is it just a number typed or a result of a formula?

Answer 2

Bear in mind I have already repaired the corruption using the exclamation tool. So, copying the cells to another sheet does as expected, that is, nothing unusual. The copied values are in the General format, just as they are on the original sheet.

You can change the format to Number in the second sheet and the Sum function still works as it should. That's quite different to how the original data was being interpreted - also in Number format. There, the original data sum was zero, because excel couldn't read those 'numbers'.

This data is hand-written, only the sum cell is generated.

Update: It's even possible to change the data to text, and excel can still sum the values. So where does this 'numbers as text' state come from?

Update: I have slightly reworded the post, to make it clear which values are the data that showed the issue - the list of values, which showed as 'numbers as text' and were non-computable, while the sum in the bottom right was incorrectly zero.

Answer 3

Hi. On a new sheet, if you first format A1:A5 as text, and enter the numbers, Excel will enter the values as text. The default cell formatting is General, which would have entered the values as values. Using number formatting on text has no affect.

Answer 4

Following those steps does indeed show the buggy behavior. So excel has two different code paths - one acknowledges the format of the cells, and one doesn't. The Format Cells tool shows incorrect state! And it presents itself as a tool for changing the state of cell formatting, when it can't.

Is this a case where Excel has been behaving badly for so long, that people have adopted a workflow to accommodate the incorrect behavior? Or is there another cell property that I'm not aware of? And where is that?

Answer 5

> ... The Format Cells tool shows incorrect state! And it presents itself as a tool for changing the state

If you type in the word "Cat", you can change the formatting to any number formatting you wish, and it will not change "Cat" into a number.