Add zeros preceding number so that number is a total of 6 characters

[Sirod]

Hello,

I have a list of product numbers of varying lengths - i.e. 2918, 5388, 37011, 47131, 765322, 872594. In a different list these same product numbers are preceded by zeros to make the number a total of 6 characters long. So in my second spreadsheet these same product numbers would appear as i.e. 002918, 005388, 037011, 047131, 765322, 872594. I need to format the product numbers in my first spreadsheet with the required number of preceding zeros. How do I do this quickly and easily? Please no VBA as I am not familiar with it.

Thanks.

 

[Joe4]

One easy way would be to create a formula like this (for an entry in cell A1):

=RIGHT("000000" & A1,6)

 

[Rick Rothstein]

On the off chance that the value in A1 could be more than 6 characters long, I would use this formula...

=TEXT(A1,"000000")