Protect (only) format of cells in calc, but NOT content

English
, ,
#1

In one sentence: I need to be able to enter a number which is not changed in any way whatsoever.

It has to stay literally exactly as I enter it, without any magic going on in the background.

Instead, whenever I enter a number in calc, it gets right adjusted, rounded, leading zeroes removed, automatically converted to date, and whatnot.

This is OK for numbers, buy I actually wanted the number being treated as text, not interpreted as a number resp. a quantity that can be displayed in different ways.

Another way to look at it:
As number, 0, 00, 00.0, 0.00 are all the same quantity (zero) and calc works with that.
But the text 0.00 and 00.0 is not the same ! And there are use-cases where the actual text is important, not the represented quantity.

How is all this related to the headline “protect (only) format of cells in calc, but NOT content” ?

It is because I thought the right way to deal with this was to use text formatting instead of number formatting. But as soon as I enter a number into a cell that was previously text-formmatted, the formatting change to number formatting, thus starting the above mentioned trouble.

I would dare to program it myself and make a pull request, but this would be a steep curve, maybe eventually leading to a reject of the pull request, finally resulting in big effort for nothing. So I start the discussion here.

Maybe someone can briefly tell how to go the pull request road, by just giving links to some one two three steps how to do it. I am just not into libre office that much. But I love it :slight_smile:

My workaround is to add a leading underscore, to force text formatting, but this is just so bad.

#2

Set the number formatting for the cell to “as text”. Then everything is taken verbatim and no numeric interpretation is attempted.

  • Select Format - Cells … or edit/create a cell style
  • Select the Numbers tab in the dialog that appears
  • In the leftmost pane - Category - select Text
  • OK

If you did it on a style, you also need to apply that style to the cell in question.

Note that if you copy a number from a cell and paste it into the previously text-formatted cell, the source cell formatting is also pasted (overriding the previous formatting of the cell). To circumvent this, you need to use “paste special” (Ctrl-Shift-V) and select Unformatted.

As a side note, the correct input for spurious “force verbatim” is not a leading space, but a leading apostrophe. If you use space, the space will be part of cell content. An apostrophe text indicator will display in the formula bar but not in the cell grid, and will not be reachable for text manipulation functions.

1 Like
#3

Thank you very much for caring. I’d like to humbly say, I am afraid you completelty did not understand. I assume it is my fault in failing to adequately explain.
So I try it again :slight_smile:
This time with steps to reproduce the issue.

  1. I enter a number.
  2. I change the cell format to text, following the steps you provided ( thank you for that )
  3. I enter another number into the same cell, say 0123, and it changes to 123, dropping the leading zero.
    I just want to enter 0123 and get 0123, not 123.
    And I dont want to do any extra step, like using the mouse or a key-combination, each time I enter a number. As I already mentioned, there are many workarounds that are not useful for me.

I simply want it to stay the number as I entered it.
I want to keep the leading zero !

#4

What specific steps do you perform to do this?

Do you enter the number by keying it in, copy/paste, drag/drop or some other method?

Note that the “text” formatting does not apply to preexisting content. It only takes effect upon subsequent entry (whereas in your case it seems to not take effect at all).

Can you upload a sample file where you have changed formatting, and verified that the “force text” does not hold?

#5

There are only 2 types of data:

  1. Text is for labels, explanations, names and for identifiers. An identifier may look like a number such as “0123”. A phone number or zip code is an identifier, an article number is an identifier, an account number is an identifier. You never calculate any sum or average from identifiers.
  2. Calculatable numbers including dates, times, currencies and booleans (True/False). True=1, False=0, every date is a day number, every time value is a fraction of a day.

If your “0123” is meant to be an identifier, there are 2 ways to enter numeric text:

  1. Type the expression with a leading apostrophe '0123. The apostrope won’t be part of the text value.
  2. Prepare the cell with number format “Text” (code @) before entering the expression.

If your “0123” is meant to be an calculatable number, just apply number format code 0000. This way you can enter 123 and the number will be displayed as 0123.

The text “0123” and the number 0123 are different values even if they look the same.

#6

“What specific steps do you perform to do this?” → The steps you provided !

“Do you […] not hold?” → I repeat the steps to reproduce, it is very simple:

  1. Enter “0123” in any cell, without the quotes.
  2. Observe you get “123” without the quotes.

I want simply this:

  1. I enter “0123”
  2. I get “0123”.
#7

I can’t upload a file that shows this, you just simply enter 0123 and get 123 thats all.

#8

Just try the 2 methods for both possible data types.

#13

Hi,

as an engineer, physican and software developer with +40 years experience, I would say, there are two things to distinguish

  1. quantities

  2. symbols

A quantity may be represented in different ways, so the literal representation is not fixed. For example: Take the mathematical constant pi.

It can be represented using a greek letter, a rouded value like 3.14, or a more precise value like 3.14159265338. Each representation has its

advantages, disadvantages and use cases.

Quantities can be used in numeric calculations.

A symbol, on the other hand, carry a meaning, or definition, not a quantity, and it can NOT be used for numeric calculations.

The cell format is a means to control th behaviour of the untderlying software behaviour to properly deal with the input in a way that is desired by the user.

Libre office assume quantities over symbols, because this is it’s main purpose - to work with quantities and calculations.

So far, so good.

I just found that there is a a behaviour in libre office calc, that is not straightforward to the user, because it has to do with internal data representation,

i.e. how software deal with the user interaction of the application.

I explained in the post what I found.

Thank you very much for your attention and explaination.

kind regards

Oliver Richter

#9

I figured it out, and I think it is a bug, but not really. I’ll explain.

  1. Reproduce:
  • create a new sheet
  • write hello in cell D6
  • click on the upper left square of the sheet to mark all cells
  • RMB → format cells
  • set numbers to text, AND ALSO …
  • set background color to blue.
    Result: The cell D6 becomes blue.
  • now enter hello in cell B3
  • repeat: click to select all, change format number to text, AND ALSO …
  • change background to say orange
    Result: You get all cells orange that are part of a square with cell B3 and D6 as corners.

This shows, that cell formatting, even if all cells are selected, is restricted to a square ares of cells which had been edited.

As a programmer, I assume that the array of cell data is expanded as you type,
and this is not regarded for “select all”.

Comined with the fact that you can not visualize the cell formatting number type as text or number, this is hidden.

So that was what made me tottally confused.

  1. Workaround:
    Just fill some cell that is right and down beyond your actual content, to “trigger” the cell data array to expand all over your supposed area of data.

  2. A fix would be:

  • the select all function not selecting cells without underlying data.
  1. A nice to have for me would be:
1 Like
#10

Anyway, the “solution” doesn’t match the headline any more,
since I completely overlooked what was going on.

#11

TextEdit.ots (10.6 KB)
This template turns Calc into a tabular text editor.

#12

I found more to this.
If I do the same thing except not formatting the background color, but the cell border,
I get ALL cells changed, but still, the other formatting only apply to cells with underlying data.
I put a screenthot that shows this.
I wrote an x in any cell, then formatted ALL cells to number = text and frame = blue.
then I entered 0123 around the cell with the x, and this is the result:

screenshot_libre_office_format_cells_with_frame
screenshot_libre_office_format_cells_with_frame923Ă—256 694 KB

What a hilarious headache that gave me :joy: :rofl:

#14

Try to disable Expand formatting (menu Tools - Options - LibreOffice Calc - General).
.
But it seems to be a bug (at least in version 7.4.7.2). If do you Undo just after “set background color to blue”, columns E:XFD remains with blue background.
.
With version 7.5.3.2 only D6 remains blue (Expand formatting is disabled). It could be a way to avoid a memory collapse.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.