hi,

i have a serial number used as part of a plant number, this section has to
be 10 digits in lenght. to build the plant number i am copying the serial
number in from another column. when the serial number is 10 everything is ok
but when it is to short i need the system to fill it up with 0 at the end or
when it is to long i need the system to take only the first 10.

thanks for the help!

This formula did it for me:
=IF(LEN(A1)=10,LEFT(A1,10),A1&REPT("0",10-LEN(A1)))
....where A1 contains the serial number. The formula uses the first ten
characters of the serial number if that serial num is 10 or more
characters in length; if it is less than 10 it fills in zeroes using
the REPT (repeat) formula.

Dave O

If the sample part number is in A1, then the corrected part number is:

=LEFT(A1,10) & REPT("0",MAX(10-LEN(A1),0))

--
Gary's Student
gsnu200703


"Little Pete" wrote:

hi,

i have a serial number used as part of a plant number, this section has to
be 10 digits in lenght. to build the plant number i am copying the serial
number in from another column. when the serial number is 10 everything is ok
but when it is to short i need the system to fill it up with 0 at the end or
when it is to long i need the system to take only the first 10.

thanks for the help!

Hi,

Another way:

=LEFT(A1&"0000000000",10)

or if your numbers are numerical only

=--LEFT(A1&"0000000000",10)

so that when Tools/Option/View - "Zero values" in unchecked the cell will
appear blank if there's no value in A1.

HTH
Jean-Guy

"Little Pete" wrote:

hi,

i have a serial number used as part of a plant number, this section has to
be 10 digits in lenght. to build the plant number i am copying the serial
number in from another column. when the serial number is 10 everything is ok
but when it is to short i need the system to fill it up with 0 at the end or
when it is to long i need the system to take only the first 10.

thanks for the help!

One mo

=LEFT(A1&REPT("0",10),10)

Little Pete wrote:

hi,

i have a serial number used as part of a plant number, this section has to
be 10 digits in lenght. to build the plant number i am copying the serial
number in from another column. when the serial number is 10 everything is ok
but when it is to short i need the system to fill it up with 0 at the end or
when it is to long i need the system to take only the first 10.

thanks for the help!

--

Dave Peterson

Hello Little Pete,

i guess i am confused

u have a PLANT with PARTS
u also have a list in column of PARTS with its particular serial number
then u need to build a Plant Number
to build the plant number i am copying the serial number in from another column.

or u like to build another serial names like "PLANTnPARTS" with something
like;
PLANT ID_SERIAL NUMBER OF PART
or
PLANT ID_{first 10 characters only) (add 0's if serial number<10 characters)
then try...
="yourPLANT ID-" & "LEFT(C1&REPT("0",10),10)"
where column C assume to contain the original serial numbers of each part.
adjust to suit your plant...
--
*****
birds of the same feather flock together..



"Little Pete" wrote:

hi,

i have a serial number used as part of a plant number, this section has to
be 10 digits in lenght. to build the plant number i am copying the serial
number in from another column. when the serial number is 10 everything is ok
but when it is to short i need the system to fill it up with 0 at the end or
when it is to long i need the system to take only the first 10.

thanks for the help!

the one i suggested will include the Plant number
yet it will not serve the post title of cell length =10.
--
*****
birds of the same feather flock together..



"driller" wrote:

Hello Little Pete,

i guess i am confused

u have a PLANT with PARTS
u also have a list in column of PARTS with its particular serial number
then u need to build a Plant Number
to build the plant number i am copying the serial number in from another column.

or u like to build another serial names like "PLANTnPARTS" with something
like;
PLANT ID_SERIAL NUMBER OF PART
or
PLANT ID_{first 10 characters only) (add 0's if serial number<10 characters)
then try...
="yourPLANT ID-" & "LEFT(C1&REPT("0",10),10)"
where column C assume to contain the original serial numbers of each part.
adjust to suit your plant...
--
*****
birds of the same feather flock together..



"Little Pete" wrote:

hi,

i have a serial number used as part of a plant number, this section has to
be 10 digits in lenght. to build the plant number i am copying the serial
number in from another column. when the serial number is 10 everything is ok
but when it is to short i need the system to fill it up with 0 at the end or
when it is to long i need the system to take only the first 10.

thanks for the help!