47

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.

Istrebitel
  • 2,963
  • 6
  • 34
  • 49
  • Not sure why this is't working for you. My OO v4.3.1 accepts and leaves your specific dataformats just as you expect 001 or 1.1.12, both in the formula bar AND the cell when the cell is formatted as text (@). If putting the ` in front is your only solution (the ` might cause calculation issues), you could considder creating a macro [shift]+[control]+v that enters a ` in front of any pasted data. Alternatively, you could use AutoHotKey to create a macro to does the same and only acts inside Calc. – Robert Ilbrink Dec 13 '12 at 09:04
  • Erm, what do you mean by @? Writing @ leaves @ displayed in the cell. Problem with formatting to text, as I described, is that when I paste, OpenOffice **still** cuts zeroes even though cell stays formatted as text. Try it yourself. Open Calc, format a column as text. Open notepad, type 00001, press enter, type 0002. Copy, paste into that column you formatted as text. Cells are still formatted as text **but leading zeroes are removed**! – Istrebitel Dec 14 '12 at 09:37
  • 9
    *Basically, I want LibreOffice Calc to do what I tell it, not what it wants.* kind of have exactly that problem quite often... – embert Oct 17 '14 at 05:33
  • Yeah. The designers of this software aren't very good. It's really awkward using a speadsheet to store account information then getting the account number wrong because it starts with a 0. Oh well, just another fine example of 'less is more.' It's a shame free software designers still can't get that. – default123 Jul 19 '23 at 14:24

8 Answers8

21

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

Algiz
  • 1,258
  • 9
  • 20
  • 3
    This doesn't make a paste into those cells behave as desired. Leading zeros are still removed. The accepted answer's solution does work, however. – Ethan Aug 31 '16 at 19:52
19

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...

Mifeet
  • 12,949
  • 5
  • 60
  • 108
Robert Ilbrink
  • 7,738
  • 2
  • 22
  • 32
  • 1
    Aha. That was unavailable and I thought it isnt working or not allowed in my case, but clicking the column actually made it active and allowed me to select text. Which made leading zeroes to correctly paste. I guess this coupled with text format on whole sheet is actually what I'm looking for. Thanks! – Istrebitel Dec 14 '12 at 11:52
  • 1
    Thank you! That works. It's frustrating though that Calc ignores the formatting selection you've already made in the destination sheet, and requires you to do it here again. – Ethan Aug 31 '16 at 19:55
5

When you are importing, you're given a bunch of options. Select "Quoted field as text" so any text inside quotes is treated as text which is interpreted by LibreOffice as sacred and they do not modify it in the way they they modify something that they identify as number

enter image description here

aaron-coding
  • 2,571
  • 1
  • 23
  • 31
  • 2
    This is the only solution that worked for me. Even after formatting the cells as text before pasting, Calc automatically removes the leading zeros. I had to surround all the numbers with double quotes just to make Calc preserve the numbers as is. Terrible. – JAT86 Aug 03 '19 at 18:53
1

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.

Michael Yurin
  • 1,021
  • 14
  • 18
0

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

kris
  • 11
  • I'm guessing he is talking about pasting. He also mentioned ' is not good for him,m as he is not typing but pasting data. – vacip Mar 12 '16 at 21:54
  • This worked for me inputting invoice numbers with leading zeros manually rather than pasting them. – Martin Sep 02 '16 at 10:19
-1

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 .

msrahlj2
  • 1
-1

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.

lee
  • 1
-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.

Christopher
  • 99
  • 1