Leading "0"s Zeros in numbering items

%3CLINGO-SUB%20id%3D%22lingo-sub-2924566%22%20slang%3D%22en-US%22%3ELeading%20%220%22s%20Zeros%20in%20numbering%20items%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2924566%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20thousands%20of%20items%20that%20need%20to%20be%20assigned%20locations%20that%20are%20in%20a%20sequence%20from%2001%20to%2019.%26nbsp%3B%20E.g.%2C%20we%20have%20location%20A-01%20through%20location%20A-19%20and%20then%20need%20to%20increment%20to%20B-01%20through%20B19%20and%20so%20on.%20To%20maintain%20location%20number%20standardization%2C%20we%20must%20include%20the%20leading%20zero%20for%20locations%20that%20are%20less%20than%2010%20BUT%20in%20order%20for%20me%20to%20use%20a%20formula%20to%20create%20the%20locations%2C%20I%20also%20need%20to%20perform%20math%20on%20the%20location%20numbers.%26nbsp%3B%20If%20I%20convert%20the%20cell%20to%20text%20format%2C%20it%20won't%20use%20the%20formula%3B%20if%20I%20leave%20the%20cell%20as%20%22General%22%20or%20Number%20(with%20no%20decimals)%2C%20it%20truncates%20the%20leading%20zero.%26nbsp%3B%20If%20I%20put%20a%20text%20%220%22%20in%20A1%20as%20a%26nbsp%3B%20reference%20cell%20and%20try%20to%20use%20%3DIF(C2%26lt%3B9%2C%24A%241%26amp%3B(C2%2B1)%2CC2%2B1)%20in%20cell%20C3%2C%20it%20deletes%20the%20leading%20zero.%26nbsp%3B%20Replicating%20the%20formula%20down%20the%20column%20only%20makes%20it%20work%20for%20every%20other%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20looked%20through%20all%20the%20formatting%20possibilities%20and%20tried%20multiple%20different%20formula%20approaches%20but%20none%20have%20thus%20far%20kept%20the%20leading%20zero.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2924566%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

We have thousands of items that need to be assigned locations that are in a sequence from 01 to 19.  E.g., we have location A-01 through location A-19 and then need to increment to B-01 through B19 and so on. To maintain location number standardization, we must include the leading zero for locations that are less than 10 BUT in order for me to use a formula to create the locations, I also need to perform math on the location numbers.  If I convert the cell to text format, it won't use the formula; if I leave the cell as "General" or Number (with no decimals), it truncates the leading zero.  If I put a text "0" in A1 as a  reference cell and try to use =IF(C2<9,$A$1&(C2+1),C2+1) in cell C3, it deletes the leading zero.  Replicating the formula down the column only makes it work for every other row.

 

I've looked through all the formatting possibilities and tried multiple different formula approaches but none have thus far kept the leading zero.

1 Reply

@Dino777 

That's either with number format 00:

=COUNTIFS(A$2:A2,A2)

 

Or with number format General:

=TEXT(COUNTIFS(A$2:A2,A2),"00")