If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Add Leading Zeros
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 |
#3
|
|||
|
|||
Add Leading Zeros
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 |
#4
|
|||
|
|||
Add Leading Zeros
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 |
#5
|
|||
|
|||
Add Leading Zeros
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 |
Thread Tools | |
Display Modes | |
|
|