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

On 06/01/2014 01:02 AM, ZP wrote:
> On Sun, 01 Jun 2014 01:11:36 +0100
> Brian Barker <b.m.barker@btinternet.com> wrote:
>
> > At 17:34 31/05/2014 -0600, Oogie McGuire wrote:
> > > I'm tearing my hair out here.
> > Don't!
> >
> > > 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?
You have two options here.:

The simplest is to change the column in Calc to text and then 
concatenate the require number of leading zeros; in a new cell 
=concatenate("00", <cell with values>)

The better solution may to change the column definition in the database 
table to use the data type varchar(6) or int. Varchar(6) allows the use 
of a variable number of characters up to 6 characters length. Int 
assumes the data is originally integer and all remain an integer. 
Normally the allowed integer is much large the 999,999.
> > 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.
> >
> > I trust this helps.
> >
> > Brian Barker
> Just change the properties to 'Text'.  It'll preserve the format and
> you can still do math functions using the cell.

--
Jay Lozier
jslozier@gmail.com


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