Fill formula help

[EYEMDABEST]

I have a spreadsheet that shows part numbers that are four and five digits. I have another that shows the same part numbers but is front filled with zeros. Is there a formula i can use to front fill with zeros the sheet that doesn't show them?

 

[Foo_Man_Chu]

Change the format of the cells on the sheet you want to have padded with zeros. Under the "format cells" menu, click the number tab and choose custom. Then put 5 zeroes in the "type" box and that should pad your numbers up to 5 characters with zeros.

HTH

 

[LCTrucido]

Change the format of the cells on the sheet you want to have padded with zeros. Under the "format cells" menu, click the number tab and choose custom. Then put 5 zeroes in the "type" box and that should pad your numbers up to 5 characters with zeros.

HTH

This was my recommendation as well, just bear in mind that it won't actually change the cell contents, just the way they're displayed. For example, 123 will look like 00123, but when you look at the cell contents in the formula bar you'll still only see 123.

This will matter if you try to run a search for 00123 or build a formula based on cell contents.

 

[T. Valko]

I have a spreadsheet that shows part numbers that are four and five digits. I have another that shows the same part numbers but is front filled with zeros. Is there a formula i can use to front fill with zeros the sheet that doesn't show them?

Does that mean you want all the part numbers to be 5 characters long?

Book1
	A	B
2	1234	01234
3	12345	12345
4	3456	03456
5	23456	23456
6	9999	09999
7	10000	10000
Sheet1

This formula entered in B2 and copied down as needed:

=IF(LEN(A2)=4,0&A2,A2&"")