adding leading 0s to the middle of a formula

[morbdetro]

Hello all. I have a workbook that auto generates a reference number. There is a macro that runs as well to generate some paperwork which is working fine, I am having trouble with the formatting of the reference number. What I have now is a number in row A that keeps increasing by 1. How I accomplish this is cell A3 would have "NCR18-001", then in A4 I have this formula.

Please Login or Register  to view this content.

the result is NCR18-2. Is there a way to tweak this formula so that it generates NCR18-002?

[ben_hensel]

Please Login or Register  to view this content.

The TEXT function converts a number to a text string in a specified format -- "000" requires that it be three digits long, so it will put leading zeros on if necessary.

[sandy666]

another way:

Please Login or Register  to view this content.

[AlKey]

Enter in A3 and drag formula down for as far as you need.

Please Login or Register  to view this content.

v	A
1
2
3	NCR18 -001
4	NCR18 -002
5	NCR18 -003
6	NCR18 -004
7	NCR18 -005
8	NCR18 -006
9	NCR18 -007
10	NCR18 -008
11	NCR18 -009

[morbdetro]

Thanks everybody for the quick replies. They all worked.

[sandy666]

You are welcome and thanks

Have a nice day