## Left Padding Zeros in Excel

Here’s a quick equation to use in Excel if you’re trying to pad a number out to the left. For instance, you have “427” and you really want it to be “0000427”:

=TEXT(A1,"0000000")

The TEXT function has a few more tricks up it’s sleeve. Here’s a link to more details on it: http://www.techonthenet.com/excel/formulas/text.php

Advertisements

Or you could just use the Format Cell option ->Custom-> “0000000”. Good to know that text formula though too!

I like this because the number remains a number and I can Fill Down sequences.

Tim, that’s true; however, it doesn’t change the underlying data, it’s simply how it’s displayed. Thus, if you were to save the file off as a csv, then you’d lose the leading 0’s.

10x, great hint!

thanks a lot

A more complicated, but more flexible option:

=CONCATENATE(REPT(“0”,(“7”-LEN(A2))),A2)

Lets you include a helper column whereby you can set the pad length for each row by replacing “7” with a value in your helper column. You can also use this to set your pad character (or a string of characters) using another helper column.

Nice, thanks Rob! I would point out that the 7 shouldn’t be in double-quotes, but regardless, this is a more flexible option.

With Excel 2007, you have to remove the quotes from both the repeating number and the number of times to repeat it.

If not, it just spits out #NAME?

Steve, my guess is you copied the formula and pasted it in Excel as is. If so, you’ll need to delete the 2 pair of double-quotes and re-type them in as they’re a formatted version of double-quotes (for lack of a better term).

Thanks for your help. It was really helpful.

Thanks, Jason. This solved the leading zero tab/csv export problem for me. Clean and simple.

So I’m a beginner. And I need to give a list to my programmers in CSV format with leading zeros. I tried both of the examples above. I got the text one to work, but only for a single cell and I don’t want to copy into 2500 cells. So I tried the repeat one that looked like it would do that, but it sets my cell the the # 0. I also get a message about a circluar reference. So I must be doing something wrong. Suggestions?

Mary,

Thanks for the message. First of all, quickly copying an equation down to 2500 cells is one of the many things Excel is great at! For instance, if your data is in cells A1:A2500 and you want your equation in B1 to be copied down through B2500, do the following:

1. Copy cell B1

2. Go to cell A1

3. Hold down the Ctrl key and push the down arrow. This should jump you down to the last row of data in column A.

4. Go over to column B in that last row

5. Hold down the Ctrl and Shift keys and then (while still holding them down) click the up arrow.

6. Hold down the Ctrl and push the v key.

Using these shortcuts is the easiest way to navigate around large sets of data. Hopefully that helps, but if not, let me know. Good luck!

It isn’t working. What are you assuming the column, to which you are padding zero’s to, is formatted in?

Thanks for the question, David. I just tested this and it doesn’t matter what the format is in. I tried typing 427 in a cell so Excel interprets it as a number and I tried typing =”427″ where Excel will interpret it as a string. Either one works with the equation I posted above. You’ll need to be more clear as to why it’s not working for you.

Jason, thank you for your response. Mine is slightly long.

I received an ascii file with one column of phone numbers in this format: 000-000-0000

Some of the numbers don’t have a prefix(the first 3 characters) and others do. Those that don’t have a Prefix are left justified. I wanted to import this file into Excel, which I did, and after identifying the type of file it was, all were imported into one column, as I expected. How can I pad those with out the first 3 numbers, and be able to export the list with the changed data? The is is 13,333 records long. A bit much to pick and choose.

I realize your formula does work if you were to type in the numbers.

I do appreciate any suggestions about this situation. Thank you.

Hi David,

So I’ll assume in column A (starting in A1), you will have phone numbers in one of two formats: 000-000-0000 or 000-0000. In that case, the formula in B1 would be:

`=IF(LEN(A1)>8,A1,"xxx"&"-"&A1)`

Just replace the xxx with whatever area code you want.

Also, to save yourself a TON of scrolling, use this sequence of keyboard shortcuts:

1. Put formula above in cell B1. Then copy B1.

2. Use your left arrow to go to A1. Then press Ctrl and the down arrow. This should jump you to the bottom of your huge dataset.

3. Right arrow over to column B of the last row. Press Shift and Ctrl and the up arrow.

4. Press Ctrl and V to paste your formula from B1 to the whole dataset.

5. Voilà!

Hope that helps!

Jason, Thank you very much for your excellent work. Your word, “Voila!” is a perfect word for this situation. Thanks again!!!!!!!

Jason, Thank you very much for your excellent work.

Your word, “Voila!” is a perfect word for this situation.

Thanks again!!!!!!!

Any Idea how to make left pad with zeros including letters?

Example

A1 –> A01

A11 –> A11

B6 –> B06

B12 –> B12

and so on

Thanks in Advance

Interesting question, Frank. Without a ton of details on what you need, I’ll make a few assumptions to make my answer work: 1. the resulting format will be “xyy” where x is a letter and yy is a 2-digit number with leading zeros, 2. There will always be a letter in the leftmost spot and there will be at least a 1-digit number following it. In that case, if you had your data in column A (starting in A1), you would use the following equation in cell B1:

`=LEFT(A1,1)&TEXT(IF(LEN(A1)=2,RIGHT(A1,1),RIGHT(A1,2)),"00")`

PERFECT. Thanks again

How about combining letters to a number sequence with padded zeroes, e.g., AZ0001, AZ0002, AR0011, AK0111….? I have state abbreviations and names; and for each name within the state, i’d like to assign a unique ID sequentially. So Column A contains 2-letter state abbreviation, in B2 I entered the formula =A2&”0001″ (Result is AZ0001), then in B3 I entered the formula =IF(A3=A2,A3&(RIGHT(A2,4)+1)),A3&”0001″). It works fine at starting over 0001 with each new state, but it doesn’t pad the zeroes when I add (+1), my result (AZ2 instead of AZ0002). Any suggestions?

Hi Miss Jenkins,

Good question. Yes, you can append your state string with a padded count of how many previous instances of that state string occur. Instead of the formula you entered in B3, use this one instead (and then copy it down for the rest of column B):

`=A3&TEXT(COUNTIF($A$2:A3,A3), "0000")`

The combination of A2 having $’s and A3 not are key in that formula because that combination of absolute and relative references give the unique count for each row.

A million thumbs up for this simple solution!! Thanks!

Thanks! Love how you updated your handle too, ha. 🙂