CODEWITHSUNDEEP
excelpadding
Mark A
Mark A

Reputation: 6171

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

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.

Upvotes: 345

Views: 687463

Answers (8)

Tms91
Tms91

Reputation: 4224

Assuming that the number you want to pad is in cell A1, and the "padding number of zeros" is 4 ,

e.g.

"23" should be "0023",
"245" should be "0245", and
"3829" should remain "3829"

then

=TEXT(A1,REPT("0",4))

Upvotes: 3

user2902302
user2902302

Reputation: 91

Even this will work nicely

REPT(0,2-LEN(F2)&F2

where 2 is total number of digits, for 0 ~ 9 -> it will display 00 to 09 rest nothing will be added.

Upvotes: 1

zgirod
zgirod

Reputation: 4379

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.

Upvotes: 22

MonoThreaded
MonoThreaded

Reputation: 12073

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

Upvotes: 16

ProVega
ProVega

Reputation: 5914

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.

enter image description here

Upvotes: 9

JeffK627
JeffK627

Reputation: 56

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.

Upvotes: 1

GSerg
GSerg

Reputation: 78210

=TEXT(A1,"0000")

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

Upvotes: 583

Moses
Moses

Reputation: 9183

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.

Upvotes: 85

Related Questions