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.

No, but that is the proper position for a "." character in the sentence according to my trusty copy of Strunk & White. Removed it for clarity. — Mark A, Oct 21 '10 at 22:35
I’d accept that except that it was in a code block, in which I tend to take things more literally. (Also, Excel stores every number as a float, so that could have been a decimal place.) — Josh Lee, Oct 21 '10 at 22:38
@josh upvote for correctly spelling "accept that except that". — Michiel van der Blonk, Sep 17 '15 at 12:22

score 548 · Accepted Answer · edited Aug 22 '13 at 16:35

548

=TEXT(A1,"0000")

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

edited Aug 22 '13 at 16:35

Martin

10,738
14
59
67

answered Oct 21 '10 at 22:38

GSerg

76,472
17
159
346

4

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. – Mark A Oct 21 '10 at 22:40
1

This works well with decimal numbers... but what about hexadecimal? I can't find a way to make leading 0s work with it... – Shadow Nov 23 '12 at 04:29
11

@shadow Hexadecimal number for Excel is a string, not a number. So handle it as a string: `=RIGHT("0000" + A1, 4)`. – GSerg Nov 23 '12 at 10:07
+1 Amazing :D Thanks for that. My only problem is it will truncate if the hex number is bigger than 4 digits, but that's not a problem in my case. – Shadow Nov 24 '12 at 02:15
Wow, this is great. Do you happen to know how Excel implements this behind the scenes? – yeenow123 Mar 05 '13 at 23:01
8

@shadow, [DEC2HEX](http://www.excelnuggets.com/Engineering/Functions/DEC2HEX) takes a places argument. So `=DEC2HEX(HEX2DEC(A1),4)` works well to pad hex numbers. – Raz Wilson Mar 12 '14 at 19:41
The provided solution works it is just that in version of office 2k13, they changed the syntax of TEXT function. it should be `=TEXT(A3;"0000000")` – Matija Jul 17 '15 at 10:34
9

@mcs They did not change the syntax. Your version of Excel [happens to use `;`](https://support.microsoft.com/en-us/kb/2853427) as the parameter separator, which it takes from your Windows language settings. – GSerg Jul 17 '15 at 13:58
Good stuff, GSerg & Raz! – Viet Jan 03 '16 at 18:25
Thanks Raz, that last suggestion of yours did the trick for me. – BrianFreud Jul 28 '16 at 15:36

score 82 · Answer 2 · edited May 22 '12 at 06:49

82

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.

edited May 22 '12 at 06:49

Community

1
1

answered Oct 21 '10 at 23:00

Moses

9,033
5
44
67

9

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 – Ashton Sheets Nov 28 '12 at 17:04
@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. – Moses Nov 28 '12 at 18:46
8

For reference, this should actually be `0000`, not `000#`. Otherwise it doesn't work for 0. – PearsonArtPhoto Mar 17 '13 at 14:26
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 . ? – Andrew Feb 25 '14 at 01:05
2

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" – Andrew Feb 25 '14 at 01:10
1

Also did not work for me using 000# but worked when I just specified custom format as 0000 for a four digit number, 00000 for five, 000000 for six etc. Obviously this is only helpful for shorter format numbers – Hilary Apr 04 '16 at 14:52

score 16 · Answer 3 · answered Mar 09 '15 at 13:28

16

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

answered Mar 09 '15 at 13:28

MonoThreaded

11,429
12
71
102

score 13 · Answer 4 · answered Mar 14 '17 at 18:05

13

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.

answered Mar 14 '17 at 18:05

zgirod

4,189
3
28
36

This worked great for padding raw text, which in my case was a hexadecimal number. – Derek Feb 22 '21 at 17:50
shouldn't it be `=RIGHT("0000" & A1; 4)` ? – Tms91 Jan 13 '23 at 17:12

score 9 · Answer 5 · answered Apr 22 '14 at 16:33

9

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

answered Apr 22 '14 at 16:33

ProVega

5,864
2
36
34

6

Great suggestion and update. Need to give Microsoft a -1 for encouraging anyone to store SSNs in a spreadsheet. – Mark A Jan 28 '15 at 17:32

score 1 · Answer 6 · answered Oct 11 '13 at 13:58

1

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.

answered Oct 11 '13 at 13:58

JeffK627

56
6

score 1 · Answer 7 · edited Mar 12 '19 at 07:11

1

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.

edited Mar 12 '19 at 07:11

Nazik

8,696
27
77
123

answered Mar 12 '19 at 06:55

user2902302

93
2

score 0 · Answer 8 · answered Jan 17 '23 at 10:18

0

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))

answered Jan 17 '23 at 10:18

Tms91

3,456
6
40
74

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

8 Answers8

Linked

Related