In this post we explain how to add leading zeros to zero pad Excel values. We provide several example of how this can be accomplished.
How To Add Leading Zeros to Zero Pad Excel Values
Let’s consider that we have a value in cell A1 of 67. But we want to display it as 00067 by preserving the length to be 5 digits long. (In other words, we don’t just want to zero pad three zeros otherwise the number 153 would become 000153 – 6 digits long!)
Option 1 – Use the RIGHT Function for zero padding in Microsoft Excel
The first option is to use the RIGHT function like so:
=RIGHT("00000" & A1, 5)Here we are prepending 5 zeros (the maximum length) with our value in cell A1 to become 0000067. We then select the RIGHT 5 digits to become 00067.
Option 2 – Use the TEXT Function for zero padding in Microsoft Excel
Another option is to use the TEXT function, which is even simpler than the RIGHT function. We can use it like so:
=TEXT(A1,"00000")Option 3 – Use cell formatting for zero padding in Microsoft Excel
The final option is to use Excel formatting to zero pad values in Microsoft Excel. Highlight the cell, right-click and choose ‘Format Cells’. Then select ‘Custom’ and in the ‘Type:’ text box paste:
0000#You can then use the format painter to apply this format to other cells. To do this, highlight the cell A1 where we have just applied the formatting. Then under the ‘Home’ tab and the ‘Clipboard’ section, select the paintbrush (Format Painter). Now drag over every cell that you want to apply the formatting to.