Rectangle 27 0

padding Add leading zeroes0's to existing Excel values to certain length?


=TEXT(A1,"0000")

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

@mcs They did not change the syntax. Your version of Excel happens to use ; as the parameter separator, which it takes from your Windows language settings.

@shadow Hexadecimal number for Excel is a string, not a number. So handle it as a string: =RIGHT("0000" + A1, 4).

@shadow, DEC2HEX takes a places argument. So =DEC2HEX(HEX2DEC(A1),4) works well to pad hex numbers.

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

OMG! Perfect! You learn something new every single day. I even have multiple dog-eared Excel books and never came across this -- will use anytime I have mangled Excel-based zip code data in the future.

This works well with decimal numbers... but what about hexadecimal? I can't find a way to make leading 0s work with it...

Note
Rectangle 27 0

padding Add leading zeroes0's to existing Excel values to certain length?


Great suggestion and update. Need to give Microsoft a -1 for encouraging anyone to store SSNs in a spreadsheet.

I am not sure if this is new in Excel 2013, but if you right-click on the column and say "Special" there is actually a pre-defined option for ZIP Code and ZIP Code + 4. Magic.

Note
Rectangle 27 0

padding Add leading zeroes0's to existing Excel values to certain length?


If you use custom formatting and need to concatenate those values elsewhere, you can copy them and Paste Special --> Values elsewhere in the sheet (or on a different sheet), then concatenate those values.

Note
Rectangle 27 0

padding Add leading zeroes0's to existing Excel values to certain length?


000#
0000
  • In the Number tab, choose Custom.
  • Set the Custom formatting to 000#. (zero zero zero #)

@AshtonSheets yes, as I noted in the answer it only displays the leading zeroes, it does nothing to the actual value. However, there have been situations where I used this instead of =text() because of its convenience and lack of formula involved.

For those with my issue above, the answer was to swap out # (hash) for 0 (zero). The # gives you no character when there's no value but the 0 gives you a zero when there's no value! Makes sense and works great: $0.0,,"M"

I use a variation of this solution to round to millions of dollars: $#,,"M" However this turns $200,000 into $.2M when I would prefer $0.2M. Then I use $0#,,"M" and it gives me my $0.2M format but also changes numbers larger than 1,000,000 into $01.0M format! I seem to be going in circles - is there a solution for getting a 0 before the . ?

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

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

This works great if there is no need to retain the leading zeroes past the original cell but won't work if the strings need to be used anywhere else. It would make the cells display as 0004, 01032, 0284 but if you tried to combine the strings in a separate cell it would show, for example, 4-1032-284 instead of the desired 0004-01032-0284

Note