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.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

1 Response

  1. Jake

    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