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

=TEXT(A1,"0000000")

=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

About these ads
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:

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

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

  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

      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!

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

      • 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?
    Example
    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:
      =LEFT(A1,1)&TEXT(IF(LEN(A1)=2,RIGHT(A1,1),RIGHT(A1,2)),"00")

      • frank
        24 November 2014 at 17:34

        PERFECT. Thanks again

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 948 other followers

%d bloggers like this: