Home > Excel > Left Padding Zeros in Excel

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”:






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

Categories: Excel Tags: , ,
  1. Tim
    27 July 2012 at 18:19

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

    • 5 December 2013 at 17:26

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

  2. hurls
    23 August 2012 at 15:46

    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.

  3. Rok
    23 March 2013 at 04:57

    10x, great hint!

  4. meokur
    9 December 2013 at 09:44

    thanks a lot

  5. Rob
    30 December 2013 at 09:58

    A more complicated, but more flexible option:


    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.

    • 6 January 2014 at 08:40

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

      • 19 April 2016 at 21:41

        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?

      • 20 April 2016 at 08:23

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

  6. Uday
    8 January 2014 at 11:15

    Thanks for your help. It was really helpful.

  7. Greg
    15 May 2014 at 08:59

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

  8. Mary Ruble
    31 October 2014 at 11:06

    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?

    • 31 October 2014 at 22:21

      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!

  9. David
    18 November 2014 at 10:56

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

    • 19 November 2014 at 12:18

      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.

      • David
        19 November 2014 at 12:39

        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.

      • 23 November 2014 at 15:18

        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:
        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!

      • David Hogue
        24 November 2014 at 12:05

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

      • David
        24 November 2014 at 12:07

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

  10. frank
    19 November 2014 at 14:36

    Any Idea how to make left pad with zeros including letters?
    A1 –> A01
    A11 –> A11
    B6 –> B06
    B12 –> B12
    and so on

    Thanks in Advance

    • 23 November 2014 at 15:10

      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:

      • frank
        24 November 2014 at 17:34

        PERFECT. Thanks again

  11. Miss Jenkins
    9 October 2015 at 12:12

    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?

    • 6 November 2015 at 09:57

      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.

      • Miss Satisfied Jenkins
        6 November 2015 at 15:13

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

      • 10 November 2015 at 08:24

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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: