make cell length always equal 10

[Guest]

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

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

 

[Guest]

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

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

 

[Guest]

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

 

[Dave Peterson]

One more:

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

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!

 

[Guest]

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

 

[Guest]

the one i suggested will include the Plant number
yet it will not serve the post title of cell length =10.