How to prevent OpenOffice/LibreOffice Calc from ch

Question

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.

Answer 1

My issue was currency, properly formatted would change to a much larger number if the numbers entered could represent a date; such as 4.22 becoming $42,482. I discovered that adding a trailing zero solved the problem.

Answer 2

I had pasted numbers from another site and it kept coming up with dates. I just messed around and hit the arrow that's on the paste board to give me the option of unformatted text or HTML format. I selected unformatted, a window opened to show me the text I wanted so I pressed o.k.

Answer 3

I initially arrived at this page with a very similar (but not identical) problem. I am posting the solution here for the benefit of those who might be visiting with the same issue.

Every time I would save, close, and then re-open my .XSLX spreadsheet in OpenOffice, it would delete the spaces I had entered in between text. For example:

• "Did not attend" would become "Didnotattend".
• "John DOE" would become "JohnDOE", etc.

Specifying "text" (@) as the format (as recommended above) did not help me, unfortunately.

What ultimately did solve it was saving it as an .ODS file instead of .XSLX .

Answer 4

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.

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

Answer 5

When you have your data in the clipboard click Edit -> Paste as... in main menu. In next window choose "Paste as text". All your data will be pasted as is.

Answer 6

just simply put character ' before the text, '0.1.16 and calc will interprate it as text data