Umbrellas – Leading Zeros (0’s) inside Excel or PowerPivot

Laura has a bunch of numbers sitting in column A5 of her Excel spreadsheet showing 5 digit product codes for her umbrella business.  She received the file from a vendor, and somewhere in the process Excel stripped off the leading zeros.  

Create a new column in Excel, then apply the following formula to apply leading zeros up to the required 5 digits :

=TEXT(A5,"00000")

 The same formula can be used inside PowerPivot.

One thought on “Umbrellas – Leading Zeros (0’s) inside Excel or PowerPivot”

  1. This is helpful if it is the same amount of zeros, but what if that is variable?

    For example, what if the data looked like this and I wanted six digits?

    1
    11
    111
    1111

    That could be a lot of if statements…

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