12

How to get number value with leading zeros in LibreOffic Calc?

FORMAT function does not want to work:

enter image description here

I need value, not just formatting.

Dims
  • 12,713

2 Answers2

13

The TEXT function can add leading zeroes without formatting the cell. It returns a string.

leading zeroes

Even though it is a string, it can still be treated as a number in other formulas. For example =D2+1 gives 2016124.

Jim K
  • 4,029
  • 1
    What is the purpose of backslash? For me it worked without it? – Dims Feb 26 '17 at 09:39
  • 1
    The backslash was borrowed from @tohuwawohu's deleted answer. According to https://help.libreoffice.org/Common/Number_Format_Codes, it makes the character literal. It does not seem necessary here. – Jim K Feb 27 '17 at 13:46
  • 1
    The backslash makes mandatory to add at least one zero. E.g.: =TEXT(123,"\000") gives 0123 while =TEXT(123,"000") gives 123. – Lucas Jun 03 '20 at 07:11
5

This answer is about formatting

I'd like to add to tohuwawohu's answer that you can also just increase the "Leading zeroes" field within the "Format Cells" dialog. This might be easier for those who prefer to use the graphical interface and mouse only.

  1. select cell, col/row or sheet;
  2. right-click, select "Format Cells..." (or Menu Format -> Cells);
  3. click on the up arrow in the "Leading zeroes" box as many times as needed: two for 01, three for 001 and so on.

P.S. I'm adding this as a separate answer because I don't have enough reputation to comment.

P.S. I gave this answer because I understood the sentence: "I need value, not just formatting." to mean something else from what you subsequently explained in a comment to tohuwawohu's answer. He consequently deleted his answer but I'm leaving mine because it has been upvoted and could be of some use even though it did not address your question in its true meaning.

simlev
  • 3,822
  • 3
  • 15
  • 33