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.

2
  • 1
    The Format runtime function is for LibreOffice Basic macros. It is not a spreadsheet function.
    – Jim K
    Feb 27, 2017 at 13:52
  • Why can't use one in another?
    – Dims
    Feb 27, 2017 at 20:24

2 Answers 2

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.

3
  • 1
    What is the purpose of backslash? For me it worked without it?
    – Dims
    Feb 26, 2017 at 9:39
  • 1
    The backslash was borrowed from @tohuwawohu's deleted answer. According to help.libreoffice.org/Common/Number_Format_Codes, it makes the character literal. It does not seem necessary here.
    – Jim K
    Feb 27, 2017 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 3, 2020 at 7: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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .