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.

  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 947 other followers

%d bloggers like this: