How to Handle the Headache of Leading Zeros in Tableau


How to Handle the Headache of Leading Zeros in Tableau

by Stephanie Kennedy

This question comes up in training almost every time:  

“My data has leading zeros. When I connect to my datasource in Tableau, it strips them off.  How can I get them back?”

How, indeed. This solution actually came from a training attendee, which pretty much adds new meaning to the phrase “train the trainer.” It works beautifully, and because Tableau allows you to save extracts out, anyone in your company that uses the data you’ve formatted will be able to partake of your greatness.

First, let’s show an example of what we mean:

Leading Zeros Stripped Example

What the user wants here is for the EmployeeID to be formatted as a six-character field, with leading zeros for values less than six characters long. To wit:







That’s not too much to ask, is it? Well ask and ye shall receive.

Get Those Zeros Back Where You Want Them

First, create a calculated field by right-clicking anywhere in the Data window in your worksheet. Then, enter this calculation:

Right(“000000” + Str([EmployeeID]),6)


  • EmployeeID” is the name of your field.
  • The number of zeros in the first section is equal to the length of your field, including the leading zeros.  
  • The number of zeros in your ‘Right’ function is the same as the number of zeros in the first part of the calculation. In this example, the field length we need, including leading zeros, is 6.

Here is what that looks like in the Calculation box in Tableau:


Here is a side-by-side comparison of the original EmployeeID and the newly formatted one in a Tableau worksheet:

Side-by-Side Employee ID Example

The absolute beauty of this trick is that it will work for any size field you need. All you have to do is change the number of zeros and the numerical value in the ‘Right’ function.

I can only assume that using leading zeros is a vestige of the olden days, back when fields all needed to be a fixed-length because database tools were not as sophisticated. It matters not how we got here, it just matters that nearly every corporation has this problem in some form or another. This is a simple and effective way to handle it, using Tableau to make your data shine!

View the results in Tableau by downloading the attached workbook below.

More About the Author

Stephanie Kennedy

Analytics Consultant
Do I Need New Running Shoes? Let the Data Decide I gave up getting angry for Lent this year, so I won’t start this blog post ranting about how difficult it is to get my data out of my ...
Off-Label Uses for Measure Names and Measure Values in Tableau Sometimes, I feel like I’m cheating the system when I find a neat way to use something that Tableau has to offer. Other times, I feel ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK


Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Geschäftsführer: Mel Stephenson

Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072


Love our blog? You should see our emails. Sign up for our newsletter!