Cell formatting issue - how can "hello" be a number?


I am confused by one thing which probably leads to other issues (SUM not calculating). Well, long story short - in steps:

  1. LibreOffice (x64)

  2. I open a new Calc spreadsheet

  3. I enter “hello” in a cell

  4. using context menu on this cell I choose “Format cells…”

  5. what I see is this (before I change anything):

The question is: how come “hello” is a number? Is it not supposed to be a string by default?

Elaboration/rant about data type vs. format

Formatting is a kind of preparation for data, to decide how it should be displayed. The format option “Number” does not instruct the cell to hold a number, but decides how to render numerical content. This is different from the data type definition for database cells. There is one notable similarity: If you format a cell as a text cells, it will subsequently not interpret numbers or formulas, but take what is entered as verbatim text input. However, if the cell already holds a number or a formula, formatting the cell as a text cell will not change existing content, only future input.

Those selectable formats listed in the dialog are “quick picks” which cater for most needs. You can actually have much more elaborate formatting by manual entry in the format code field. You can use up to 4 different format specifiers, the 3 first for different number ranges (default to positive/negative/zero, but this is also configurable) and the fourth for textual data (input which is explicitly given as text, or which does not have an unambiguous numerical interpretation). See the help system for details.

How to get data type

You can use the CELL() function to find out which data type the cell holds. =CELL(<address>;"TYPE") returns …

  • b if the cell is empty (Blank)
  • l if the cell holds text (Label ; this is the type also returned for an empty text string)
  • v if the cell holds a number (Value)

The CELL() function does not distinguish between entered data and the result of a formula. You can distinguish between those with =ISNA(FORMULA(<address>))

Very informative, thank you. Yes, I was actually thinking about the spreadsheet cells as if they were database fields or program language variables.

It is not “hello is a number”, it is “cell B3 has number format”. And that is the default format for any cell, until you do something that changes the format. That is normal, and doesn’t prevent your cell to actually contain a textual content.

I see, thank you. BTW - how do I get the cell content type? I mean whether it is a string, or int…
EDIT: well, weird thing is that a string is formatted as a number by default (I guess it does not work this way in Excel). But we won’t fix it now, that’s for the devs. :slight_smile:

Depends on what you mean by “getting”. You may use spreadsheet functions for that like ISNUMBER or ISNONTEXT. You may use value highlighting. You may even use status bar with its average/sum display, so that when you click on a cell, you see if the status bar displays a number…

I guess it does not work this way in Excel

  1. Again: it’s not “a string is formatted as a number by default”, it’s “you enter a string into a cell that is already formatted as number”.
  2. It works in Excel just the same way (its General format is a number format without any specific formatting rules).