Re: [libreoffice-users] Can't get leading zeros in Calc

At 13:02 01/06/2014 +0800, Zonly Ponly wrote:
> On Sun, 01 Jun 2014 01:11:36 +0100 Brian Barker wrote:
> > At 17:34 31/05/2014 -0600, Oogie McGuire wrote:
> > > I have a spreadsheet and the data was originally entered as 4 
> > > digits. I need to pass it to a database system that requires 6 
> > > digits. I've tried formatting with 2 leading zeros but I still 
> > > cannot get the number to properly show up as 00<number>. Any ideas?
> >
> > Yes. If the values you have are numbers, then formatting them (as 
> > something like "000000") should display them as you need. Whether 
> > you get six digits into your database depends on exactly how you 
> > then transfer the values.
> >
> > But you say this doesn't work. The most likely explanation is that 
> > the values stored in the cells are not numbers but text strings - 
> > albeit made up of four numeric characters. Changing the formatting 
> > of such cells after the event will not change text values into 
> > numbers. (You generally wouldn't want it to.)
> >
> > How to proceed? Take your pick:
> >
> > o In a new column, enter =VALUE(Xn) and fill it down the column. 
> > You will now have numbers and can format them as you wish. You 
> > could even copy them back over the original values, using Paste 
> > Special... and pasting Numbers but not Formulas.
> >
> > o In a new column, enter ="00"&Xn and fill it down the column. You 
> > will now have six-character text values. Again, you could copy 
> > these back over the original values, using Paste Special... and 
> > pasting Text but not Formulas.
>
> Just change the properties to 'Text'. It'll preserve the format and 
> you can still do math functions using the cell.

Sorry, but this is rather confused. You talk of changing the 
"properties"; do you mean the cell format or something else? Surely 
the questioner's problem is the reverse of what you describe: that 
his values are already text and thus do not respond to changes in 
Number format.

You can change the cell format to Text only if it is not already 
Text. But if the questioner's values were numbers formatted as 
Number, he would not have his problem: just setting the format to 
000000 would achieve what he needs. In any case, if a number 
formatted in this way has leading zeroes, changing its format to Text 
would not - as you claim - preserve this format. The value would stay 
as a number (even in a text-formatted cell), but would lose its 
leading zeroes, returning to a default numeric format.

It's unlikely anyway that he needs to carry out calculations with 
these values: it's only numbers used as labels - such as postal codes 
and telephone numbers - that need leading zeroes, not numbers used as values.

Brian Barker  


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted