Sometimes you want
numbers of fixed width so you want numbers with preceding zeros such as
000xxxx. Excel will normally dismiss the zeros and record only xxxx where
the first x from the left represents the first nonzero. eg If you type
000000405670 Excel will record 405670. It deletes all the zeros to the
left of the first nonzero digit. There are two solutions.
1. Specify that the
format of the cell is to be text before entering the data. Select the
cell you wish to enter a number in then in the toolbar select "Format",
"Cells", "Text". Now enter the number with preceding
zeros and the number will appear as text with zeros at the start. Then
copy the trend down the page.
2. Specify that the
format is a custom format. Select the cell you wish to enter a number
in then in the toolbar select "Format", "Cells", "Number",
"Custom" and then in the box under the word "Type:"
insert your format. eg "000##" This will ensure that the displayed
number has at least 5 digits and if it needs extra digits it will add
zeros to the front of the number. Then copy the trend down the page.
For variable numbers beginning with one or more letters, Excel is smart enough to continue
the trend. eg HG0458 will trend down such that the next number is HG0459
and so on.
Excel in Office 2000
& 2003, and maybe other versions, does not allow the use of numbers
with more than 15 digits. This is usually enough for barcodes but sometimes
you need more than 15 digits. Numbers of more than 15 characters cannot
be stored in access or excel but can be stored as text. Excel will not
fill empty cells if the text consisting of more than 15 characters. For
numbers, Excel will incrementally fill empty cells if you drag the bottom
right hand corner of a selection of adjacent cells that exhibit a incremental
trend. For text, Excel will not incrementally fill empty cells if you
drag the bottom right hand corner of a selection of adjacent cells that
exhibit a incremental trend.
If you need to generate numbers with more than 15 digits in Excel consider saving the last 15
digits in a cell & the remainder, as though they are a prefix, in
another cell You can either use the 2 data fields as they are or concatenate
to automatically create text. This same method can be used for up to 32767
alphanumeric characters in each cell of Excel 2000 by combining multiple
cells. This should be more than enough for any variable data. The diagram
below shows what I mean. The second column shows the last 15 or less digits
of the number. The first column contains what digits will not fit into
the second column. In the third column is the concatenated data, that
is the result of using "&" in a formula. The "&"
function combines two pieces of text.
Prefix 
Varying Portion 
18 Character Number Using the Formula (=A8&B8) 
123 
456789012345678 
123456789012345678 
123 
456789012345679 
123456789012345679 
123 
456789012345680 
123456789012345680 
123 
456789012345681 
123456789012345681 
123 
456789012345682 
123456789012345682 
123 
456789012345683 
123456789012345683 
Here
is the actual Excel file called "Numbers_with_more_than_15_digits.xls"
that contains the table above.
