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

Question

There are many  many questions and quality answers on SO regarding how to prevent leading zeroes from getting stripped when importing to or exporting from Excel   However  I already have a spreadsheet that has values in it that were truncated as numbers when  in fact  they should have been handled as strings   I need to clean up the data and add the leading zeros back in   There is a field that should be four characters with lead zeros padding out the string to four characters   However    23  should be  0023     245  should be  0245   and  3829  should remain  3829    Question  Is there an Excel formula to pad these 0 s back onto these values so that they are all four characters   Note  this is similar to the age old Zip Code problem where New England-area zip codes get their leading zero dropped and you have to add them back in

User · Answer

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.

User · Answer

The more efficient  less obtrusive  way of doing this is through custom formatting    Highlight the column array you want to style  Click ctrl   1 or Format -  Format Cells  In the Number tab  choose Custom  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

User · Answer

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

User · Answer

Even this will work nicely  REPT 0 2-LEN F2  amp F2   where 2 is total number of digits  for 0   9 -  it will display 00 to 09 rest nothing will be added

User · Answer

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

User · Answer

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

User · Answer

TEXT A1  0000     However the TEXT function is able to do other fancy stuff like date formating  aswell

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

Examples related to excel

Examples related to padding