On 06/01/2014 01:02 AM, ZP wrote:
On Sun, 01 Jun 2014 01:11:36 +0100
Brian Barker <b.m.bar...@btinternet.com> wrote:
At 17:34 31/05/2014 -0600, Oogie McGuire wrote:
I'm tearing my hair out here.
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.
Just change the properties to 'Text'. It'll preserve the format and
you can still do math functions using the cell.
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
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