Here's our Microsoft Office Tip for.. January 20, 2003
Click Here to see a complete list of all of our previously released Tips!
Leading Zeros
There are a number of solutions for adding leading zeros to a value, and some of them are more convoluted than they need to be. If you're using VBA, we can offer a simple method for adding leading zeros, but it isn't all that intuitive. However, it's simple and it works.

In a nutshell, you add the value you want to display with leading zeroes to a 10-based number that's one place larger than the number of characters you need for each entry. For instance, if you want all values to have five characters, using leading zeros to fill in as needed, you'd use the number 100000--that's one place more than five. The function

Right(value + 100000, 5)

will return five characters from the result of adding value to 100000--including leading zeros. If value equals 30, the function will return 00030; a value of 4321 will return 04321, and so on.

If you'd like to test this function, open the Visual Basic Editor by pressing Alt-F11. Next, in the Immediate Window, type the statement

?Right(30 + 100000, 5)

and then press Enter. VBA will return the string 00030.

The following procedure will do the same--as long as you pass it a value. If the value you pass is larger than six places, the function will truncate it to the last five values.

Function LeadingZeroes(value As Long) As Variant LeadingZeroes = Right(value + 100000, 5) End Function

You can test this procedure in the Immediate window by running the statement


which should return the variant value 00030.