Add Leading Zeroes with REPT

REPT is one of those functions in Excel that most users find obscure and of little use. I have to admit that I was one of those users until I started investigating this strange little function with a keener eye to what it can accomplish.

As illustrated in the following, REPT simply returns a specified text string a stated number of times: =REPT(“Text”, Number of times)

So, what is an example of one of the many ways that REPT can help us solve a curious little problem in Excel.

As you probably know, Excel removes any leading zeroes in a number. There are, however, a number of instances where these leading zeroes are needed.

For instance, let’s assume you want to convert some raw numbers to System Codes for your company? Let’s also assume that the System Codes have 8 characters and start with a zero if less than 8 characters. In the illustrated table below, you can start with the following function in B2, and drag it down to B8: =CONCATENATE(REPT(0,8-LEN(A2)),A2)

Since “8-LEN(A2)” calculates the number of times to repeat zero, then the number of times the leading zeros are repeated is 8 minus the length of the numbers. The CONCATENATE function then simply joins the repeated zeros with the number.

Nice! Just one simple way to use the often-overlooked and slightly mysterious REPT function!