Tableau Tip: All about Leading and Trailing Zeros
Handling numbers as text for the first time in Tableau is a frustrating experience. Depending on your requirements, you might have to add /remove trailing/ leading zeros, modify number of decimal places etc. This post is an attempt to address the most common scenarios.
Note: If the final result required is a number, you can simply convert the string to number and apply Tableau’s built in format rules. This post assumes that the required result is still a string.
1. Removing Leading Zeros
This is a common scenario for formatted IDs which are padded with x number of zeros.
This can be achieved using below calculation. It looks for strings starting with 1 to 5 zeros and replaces it with an empty string.
2. Removing Trailing Zeros
This scenario normally happens when you want to remove the extra zeros after the decimal point.
This can be achieved using below calculation. It will look for strings ending with 1 to 3 zeros and an optional decimal point, then replace it with an empty string.
3. Adding Leading Zeros
This is the reverse of scenario 1 where you want to pad a list of IDs with zeros to make it 6 digits.
It is done using below simple calculation
4. Adding Trailing Zeros
This is the reverse of scenario 2 and is slightly more difficult than the other cases. If you are handling it as a number, you can simply define the number of decimal places, but what if you have to handle it as string?
You can use below calculation to attain this result. it involves following steps:
- Split the string by based on the decimal point as integer part and fractional part(if any)
- Pad the fractional part by adding enough number of zeros
- Rejoin the integer and fractional part using decimal point
+ LEFT(SPLIT([Original String],".",2)+"000",3)