Why do numbers return as ### (hashtags) when columns are too narrow, but text flows over into the next?

[rinypa]

I am new to Calc; my main base of experience is with Google Sheets.

Sheets by default allows cell content of any format to spill beyond the column boundary if there is nothing in the next cell. As with Calc, there is a “wrap text” toggle button, but in Sheets it also includes options to truncate at the column line; if this is elected and the column is too narrow, then the cell will display as much of the data as can be seen. There is no use of “###” (hashtags aka pound signs) to obscure the data.

Calc, by contrast, allows alphabetic content that is too wide to spill over into the adjacent cells (aka cross the column line), but numbers, even when formatted as text, get hashtagged.

It seems so odd that this behavior is not only default but unchangeable – just using numerals means you have no choice but to widen columns? (text wrap does not work even when I widen the row) – that I feel like I must be missing something!

[Hrbrgr]

This is clearly a question for the developers.

Unfortunately, you are in the wrong place on this page.
On this site, users help other users.

You can read about [text wrapping in cells](https://helponline.libreoffice.org/7.1/en-GB/text/shared/guide/breaking_lines.html) here.

[EarnestAl]

You might as well ask Google why a right-aligned number suddenly becomes left-aligned.

In Google, if a long number has the cell next to it filled, it appears truncated, only fully visible in the formula bar. if the number gets too big in Calc it is formatted in scientific notation so the magnitude is still obvious.

So there are differences. In this case Google is different to other spreadsheets so perhaps better posted on their forum.

[mikekaganski]

It is because flowing to the next cell would result in truncation when next cell contains something. In case of numbers, that could create wrong numeric output in cells.

Consider a 5-digit integer in a narrow cell, which could only show you 3 digits. Instead of 12345, you would see (and print) 123, and that would go unnoticed.

It is thought that it’s of a less importance for text, as (probably) not affecting calculated values. Personally I’d also show hashes if I had a chance to change it now, but that would be backward-incompatible.

See also tdf#129847 (but the arrow that proposed there in addition to hashes is for display only, not for printing).

[anon73440385]

but numbers, even when formatted as text, get hashtagged.

You can’t format numbers as text so the are really text (they keep to be numbers) - The only thing changing is the alignment of the number (it get’s left aligned).

[rinypa]

thank you mike kaganski. (I would upvote you but I’m too much of a “novice” on the boards so I’m not allowed!) The hashtagging does seem to confuse and frustrate a lot of “novice” users. People use spreadsheets for all manner of things, not just calculating data, so having control over hashtags vs. red triangles vs. overflow would maximize the ability of Calc to handle different tasks.

[Wanderer]

As your question is “why” I’d answer it is a wise decision, because Text can only flow in the next cell, if there is free space. Otherwise the text is truncated.

While web can often easily detect if text is trunc… it is not so easy to detect 34 as truncated form of 1234 and so this should be avoided.

If you want to get the same behaviour you may convert the numeric value with the function TEXT() see here
As the result IS text you can’t do calculations with this, so be warned to take this route.

J.

[rinypa]

thank you J. As with Mike above I would love to upvote you but with only 1 karma point not able to! True, I am interested in the why question, but really my question was, “is this really the case, am I missing anything?”

There’s very little documentation on this, despite being a hurdle that most new users will encounter within minutes of starting to use Calc. You can’t even search “###” on these boards or the online help!

I found only one mention of it, deep in Appendix C of the user guide. The given solution was “make columns wider” (not possible in my case) or “turn on text wrap” (does not work with numbers as shown in the screenshot).

So I really appreciate your answer about =TEXT! In my screenshot, the button for “format as number” is switched off, so I thought this made it text. I can see that it does make sense to force numerals into number format, and to require a function to treat them differently, but it’s not super-intuitive to new users, so thank you for your help!