Add leading zeroes/0's to existing Excel values to certain length

Solution 1:

=TEXT(A1,"0000")

However the TEXT function is able to do other fancy stuff like date formating, aswell.

Solution 2:

The more efficient (less obtrusive) way of doing this is through custom formatting.

1. Highlight the column/array you want to style.
2. Click ctrl + 1 or Format -> Format Cells.
3. In the Number tab, choose Custom.
4. Set the Custom formatting to 000#. (zero zero zero #)

Note that this does not actually change the value of the cell. It only displays the leading zeroes in the worksheet.

Solution 3:

I hit this page trying to pad hexadecimal values when I realized that DEC2HEX() provides that very feature for free.

You just need to add a second parameter. For example, tying to turn 12 into 0C
DEC2HEX(12,2) => 0C
DEC2HEX(12,4) => 000C
... and so on

Solution 4:

I know this was answered a while ago but just chiming with a simple solution here that I am surprised wasn't mentioned.

=RIGHT("0000" & A1, 4)

Whenever I need to pad I use something like the above. Personally I find it the simplest solution and easier to read.