make cell length always equal 10

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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))
 
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
 
One more:

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

Little said:
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!
Click to expand...
 
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.
Click to expand...

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...
 
the one i suggested will include the Plant number
yet it will not serve the post title of cell length =10.
 
Back
Top