Excel Leading Zero Format

[Closed]
Report
-
 PRM -
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
4
Registration date
Thursday March 19, 2009
Status
Member
Last seen
April 7, 2009