Adding leading zeros in front of values and keeping a fixed length

Those who work with ASCII files on a regular basis often need to add zeros (or other characters) to the beginning or end of their values. Here's the easiest way to do this in Excel.

Analysis of the Excel formula that will help us

Assuming that in cell A1 you have a value of 100 and you want to convert it to 000100 (that is, you add 3 zeros at the beginning, to have 6 characters in total), then the function to use in any Excel cell is the following :

= REPT("0", 6-LEN(A1)) & A1

Let's look at the function, section by section:

The 2 main parts of the function are:

1. The REPT("0", 6-LEN(A1)) which calculates the number of zeros to be entered at the beginning of the value. The REPT function repeats one or more characters the number times we define.

2. The & A1 part which simply adds the initial value 100 of cell A1 to the zeros that will be created by the first part.

Section (1) is also divided into 2 sub-sections:

a. The REPT("0",) specifies that the character we want to repeat is zero.

b. The 6-LEN(A1) which specifies the number of zeros we want to create. It is calculated by subtracting the character length of the initial value (100) from the total character length we want our final value to have. The LEN function calculates the number of characters a cell contains.

The value 100 consists of 3 characters and we want the final value to have 6 characters. So 6-3 = 3 zeros will be created and put in front of 100 = 000100.

Variations of the formula

You can replace "0" in section (1a) with any other character; in the example above if you use "A" you will get the value: AAA100.

You can change the order of the two key parts and put the characters at the end of your initial value, for example = A1 & REPT ("$",6-LEN(A1)) will return the value: 100$$$.

And of course you can change the 6 used in the above function and set your own number of characters that you need for the final value.