Inappropriate display of formulae when whole sheet set to Text

[rdreLO]

I routinely set multiple whole columns to Text format rather than numbers. Text-based formulae can be defined when the column is already set to Text, and they work fine - the cells show the values, as expected, not the formula. I often require all columns to be Text by default as I add new columns. You would think that the sensible thing to do is to click on the (0,0) cell to select the whole spreadsheet and set all cells to Text. However, if I do that, then all cells no longer show the values resulting from the formulae, only the formula definitions.

NOTE: the following are completely INEFFECTIVE once the spreadsheet starts behaving like this:

1. View->Show formula
2. resetting the data type of individual cells or columns with Format->Cells.

The only thing that works is to remove all direct formatting, then reset the format to Text, but on SUBSETS ONLY of the whole spreadsheet.

It is quite illogical that a setting that works on an arbitrarily large number of selected columns suddenly fails when you select ALL columns. Can I stop it doing this?

[mikekaganski]

Text-based formulae can be defined when the column is already set to Text, and they work fine - the cells show the values, as expected, not the formula

Are you sure? Setting any cell (selected individually; as part of a selected range, including whole columns/rows; or when selecting whole sheet) to have Text format, and then writing any formula there (be it “text-based” or otherwize) makes LibreOffice treat anything that you write into the cell as text - and thus all the input (that equal sign, function names, cell references, operators, etc) is treated as just characters of text that should be shows verbatim, not evaluated.

Just tested, and e.g. setting B2 to Text, then inputting ="a" there shows ="a" in the cell, not a.

So the question is based on wrong assumption (“It is quite illogical that a setting that works on an arbitrarily large number of selected columns suddenly fails when you select ALL columns”).

Note that setting Text format after you have set a cell’s data does not change the data type; thus if a cell already contains a formula, it will still continue to work as formula.

[rdreLO]

Thank you - I see that I wasn’t doing what I thought I was doing. In coming back to Excel & Calc intermittently, I forget about this quirk that Format → Cells won’t reset the data type to what you request, once there is data in a cell ! IMO it is a very irritating and counter-intuitive features of Calc - I don’t remember if Excel did that too, though I used it for decades. As I understand it, LO aims to have the same functionality as MSO, so if Excel did that, then I suppose we’re stuck with it. Damned annoying though.

[mikekaganski]

Format → Cells won’t reset the data type to what you request

Changing format is not requesting change of data type. Format is how data is displayed. To change data type (i. e., actually change data in cells, sometimes irreversibly loosing some data like leading zeroes) , there is a dedicated Text to Columns function.