Excel Leading Zero FormatClosed

Question

Hello,
  I would like some advice on formatting cells to have leading zeroes.  I do know about using ="0####", but my problem is somewhat more complex.

The data in the cell can vary from 10 digits to 11, to 12.  What I need is that whenever the LEN=10, append 00 to the string (ie. "00##########), when LEN=11, "0###########"), otherwise no change. 

I've tried using a custom cell format, but it's tedious to go through many dozens of cells applying the correct format, not to mention that the data is then only masked and can't be exported to a CSV for example. 

Any help would be greatly appreciated.

Thanks

PRM · Accepted Answer

=REPT(0,10-LEN(A1))&A1

where 10 equals the number of places you want including any /  eg. 123456/7 will become 00123456/7

the quirky part when using a / is that it interprets it as a divide by.  use your source as FORMAT - NUMBER 'TEXT', place your formula underneath, do it once, drag it down (relates formula to each source entry) and make those FORMAT-NUMBER 'CUSTOM'

dvi40 · Answer

hi,try this link to get help:
https://www.officearticles.com/excel/show_leading_zeroes_in_microsoft_excel.htm
hope it works.

Excel Leading Zero Format

2 responses

Similar discussions

Subscribe To Our Newsletter!