Add Leading Zeros

I have a list of product codes, all product codes should be 9 characters. In
my list I have some product codes that are not 9 characters because the
leading zeros have been dropped. Is there a formula or format I could use
that will add back the zeros if the product number is less then 9 char. I
need it to add as many zeros as necessary to make the total characters 9.

Example
100ab - this needs 4 zeros 0000100ab
100WMVF1 - this would need 1 leading zero

Enter an Apostrophe in the Cell first or Format the cells as text BEFORE
keying in the "numbers"

Erika wrote:

I have a list of product codes, all product codes should be 9 characters. In
my list I have some product codes that are not 9 characters because the
leading zeros have been dropped. Is there a formula or format I could use
that will add back the zeros if the product number is less then 9 char. I
need it to add as many zeros as necessary to make the total characters 9.

Example
100ab - this needs 4 zeros 0000100ab
100WMVF1 - this would need 1 leading zero

Hi,

If you importing this data then format as text before the import and the
leading zeros will be retained.

Or with a helper column try this form a product code in a1

=REPT("0",9-LEN(A1))&A1

Mike

"Erika" wrote:

I have a list of product codes, all product codes should be 9 characters. In
my list I have some product codes that are not 9 characters because the
leading zeros have been dropped. Is there a formula or format I could use
that will add back the zeros if the product number is less then 9 char. I
need it to add as many zeros as necessary to make the total characters 9.

Example
100ab - this needs 4 zeros 0000100ab
100WMVF1 - this would need 1 leading zero

My list is imported, this formula is exactly what I am looking for. Thank
you so much!

"Mike H" wrote:

Hi,

If you importing this data then format as text before the import and the
leading zeros will be retained.

Or with a helper column try this form a product code in a1

=REPT("0",9-LEN(A1))&A1

Mike

"Erika" wrote:

I have a list of product codes, all product codes should be 9 characters. In
my list I have some product codes that are not 9 characters because the
leading zeros have been dropped. Is there a formula or format I could use
that will add back the zeros if the product number is less then 9 char. I
need it to add as many zeros as necessary to make the total characters 9.

Example
100ab - this needs 4 zeros 0000100ab
100WMVF1 - this would need 1 leading zero

Hi,

You can also use

=RIGHT(REPT(0,8)&A1,9)

or

=RIGHT("00000000"&A1,9)

If you know that there will always be 5 characters or more in the import
data this last case can be shortened to

=RIGHT("0000"&A1,9)

and shortening the previous suggestion to

=REPT(0,9-LEN(A1))&A1

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Erika" wrote:

I have a list of product codes, all product codes should be 9 characters. In
my list I have some product codes that are not 9 characters because the
leading zeros have been dropped. Is there a formula or format I could use
that will add back the zeros if the product number is less then 9 char. I
need it to add as many zeros as necessary to make the total characters 9.

Example
100ab - this needs 4 zeros 0000100ab
100WMVF1 - this would need 1 leading zero