Excel Leading Zero Format

Closed
a68tbird - Mar 24, 2009 at 10:46 AM
 PRM - May 25, 2010 at 02:10 AM
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

2 replies

=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'
5
dvi40
Posts
4
Registration date
Thursday March 19, 2009
Status
Member
Last seen
April 7, 2009

Mar 25, 2009 at 02:15 AM
0