Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to prevent OpenOffice/LibreOffice Calc from changing what you input (data, numbers,...)

Basically, I want LibreOffice Calc to do what I tell it, not what it wants.

For example:

  • when I input 1.1.12, I want to have 1.1.12 in that cell, not 01.01.2012 or whatever.
  • when I input 001, I want to have 001 in that cell, not 1
  • and so on and so forth

I want it to never ever touch my data until I explicitly tell it to. Is that possible at all?

I know I can set format of a cell to text. It doesn't help at all. Example:

  • Input 1.1.12, it gets displayed as 01.01.12, format as text, it becomes "40909", original input is lost
  • Format empty cells as text. Paste "000 001 002 ..." separated by line breaks. Displays "0 1 2 ..."

I know I can write ' in front of anything for it to be forced text. Again it doesn't help, because when I paste in text, I cannot have ' auto-appended to it.

I hope this is possible. I tried googling for different problems and never found a good answer.

like image 397
Istrebitel Avatar asked Dec 12 '12 07:12

Istrebitel


People also ask

How do I change the data type in LibreOffice Calc?

To open this window click the icon in the sidebar or use the context menu item Format cells. Choose the Category and the Format type. A preview of how data is displayed is shown on the right of the Cell formatting window. If you must use a formatting from a different locale change the Language in the drop down menu.

How do you AutoFill numbers in LibreOffice?

Before filling a series, first select the cell range. Choose Sheet - Fill Cells - Series. To automatically continue a series using the assumed completion rules, choose the AutoFill option after opening the Fill Series dialog.


2 Answers

If you want your input to be interpreted as text and preventing Calc to do fancy (and annoying) things with your input, you have to change the format before entering any value.

  • Select the cells/columns/rows.
  • Right-click 'Format Cells...'
  • Select the tab 'Numbers'
  • In the list 'Category', select 'Text' (the last option)
  • Select the format '@' (it is the only one in this category)
  • Click on 'Ok'

You may need to tweak the 'autocorrect' options as well. Go to 'Tools > Auotcorrect Options...'. Here is a link that may help: https://help.libreoffice.org/Calc/Deactivating_Automatic_Changes

like image 200
Algiz Avatar answered Sep 30 '22 06:09

Algiz


I understand your problem with pasting pure unformatted text. This may be more work than you like (we can try to automate that later) but when I paste data from Notepad, I am prompted with an import screen as you can see below. Select the column header(s) and then select Column type: Text. This should solve your paste/import problem. An alternative is to handle this with an AutoHotKey script.enter image description here

Oh b.t.w. the @ is the format type for text, just like you have HH for 24 hour or ddd for weekdays...

like image 37
Robert Ilbrink Avatar answered Sep 30 '22 07:09

Robert Ilbrink