To change how cell is displayed

[john1023]

1. Open Calc. Select some cell. Ensure the cell format is “Number”:

Context Menu > Format Cells > Numbers > Category = Number

2. In the selected cell, type “= foo bar” (without quotes).

It will be an error: “Err:509”.

3. Change the format of this cell from “Number” to “Text”.

4. See that “Err:509” wasn’t changed to “= foo bar”. It is still “Err:509”.

To actually change how the cell is displayed, It will be necessary to add some symbol on its end (so it will be “= foo bara”), and then remove it (so it will be “= foo bar” without “a”).

It is very inconvenient. Especially when you have a lot of such cells. Is there some clever way to do it?

[anon73440385]

Why do you expect convenience for erroneous use of a tool? Don’t blame the knife, if you try to cut your steak with the blunt edge (see LibreOffice Help - Error Codes in LibreOffice Calc)

[john1023]

Opaque, sorry, but I don’t agree. The “Text” format should display the text in the cell literally. If you have a number cell and then it was converted to the “Text” cell, the text should be converted from “smart” form to “literal” form. And it is actually possible, but you need to add-remove a symbol. It is a bug and I already reported it today, but I’m searching for some good workaround.

[anon73440385]

You are correct about The “Text” format should display the text in the cell literally - but: = starts a formula and not text and therefore you get "Err:509". If you want to insert text just write foo bar into the cell. If you want to stick using a formula, you need to use ="foo bar". All all - using = foo bar is incorrect use of the tool and error code tells you about that.

[mikekaganski]

@john1023:

changing format of a cell never changes the contents of a cell. When you put that string into a cell, it is recognized as a formula, processed (resulting in invalid tokens), and put into cell’s formula data. What you are asking for is that formatting the cell remove the processed data from formula, and place into text. This is changing cell contents.

What format does is if applicable, changes the display of the current contents. For error, formatting as text does nothing. The behaviour is consistent and correct.

Formatting cells is very common and usually broad operation, on big amounts of data (thousands of rows), where, if changing format could silently change the contents, it would be easily overlooked, and highly possible result in dataloss.

[john1023]

@mikekaganski Yes, what you described is exactly what I’m searching for. The data should be automatically processed in both directions, forth-and-back, instead of just forth. Thanks a lot. I don’t agree, however, with you rationale about why the current behavior is “correct”. It can, probably, work as a “safety belt”, but it is illogical. (My personal opinion, not going to start a flame.)

[mikekaganski]

This is not “my rationale”; this is what I learned from maintainers about why is it so - when I initially suggested what you are asking now. Since then, I realized that it is correct, so it is now both my opinion, and the actual reason to implement it this way (not saying that it’s the industry standard).

[john1023]

@mikekaganski This is useful to know, thanks.

[pbhj]

Ah, I saw your question and answered it at https://superuser.com/questions/1514910/to-change-how-cell-is-displayed – you can search and replace the “=” from a selection of cells and that will convert them to text fields. Obviously you’d need to be more careful with cells like “= foo = bar”, and use a regex like “^=” as the search string.

[john1023]

The case with “=” is simply an example. In real life, it could be a lot of different cases.

[mikekaganski]

In fact, you don’t need any tricks with removal of =. Set cell format of a range to Text; Ctrl+H (Find & Replace); Find → .*; replace → $0; Replace All; [x] current selection only; [x] regular expressions. That’s all. See FAQ.