Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Remove Leading Zeros in Tableau

Cody Schneider••8 min read

Leading zeros in your data can be a real headache in Tableau, unintentionally turning clean product IDs or zip codes into messy text strings that won't sort or calculate correctly. Fortunately, removing them is straightforward once you know the right technique for your specific use case. This tutorial will walk you through several easy and effective methods for getting rid of those pesky leading zeros to clean up your data for good.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Why Bother Removing Leading Zeros?

You might wonder if it’s worth the effort to clean up a few extra zeros. In most cases, it absolutely is. Addressing them at the start of your analysis can save you from bigger problems down the road. Here’s why:

  • Incorrect Data Typing: The most common issue is that a field with leading zeros (like '00789') will be interpreted by Tableau as a string (text) instead of a number. This prevents you from performing any mathematical operations on it, like summing up sales by Product ID or calculating an average customer number.
  • Flawed Sorting: When treated as text, data doesn’t sort numerically. For example, a list of IDs like '100', '020', and '3' would sort incorrectly as '020', '100', '3' instead of the logical numeric order of 3, 20, 100. This can make your tables and charts misleading and hard to read.
  • Joining and Relationship Issues: If you are joining data from two different systems, one might store an ID as 12345 (a number) while the other stores it as 0012345 (a string). Without cleaning the leading zeros, these identical IDs won't match, leading to incomplete or failed joins in Tableau.
  • Visual Clutter: From a dashboard design perspective, 789 simply looks cleaner and is easier to read in a chart or a table than 0000789. Removing them improves the professional look and legibility of your final report.

Taking a moment to standardize these fields ensures your data is accurate, your calculations are reliable, and your dashboards are clear and professional.

Method 1: The Quickest Fix - Change the Data Type

If your column only contains numbers and simply has leading zeros (e.g., '01', '0023', '00099'), the fastest way to fix it is by changing its data type. Tableau is smart enough to handle the conversion for you automatically.

This is best done on the Data Source screen, right after you've connected to your data.

Here’s how:

  1. Navigate to the Data Source tab in the bottom-left corner of your Tableau workbook.
  2. In the data preview pane at the bottom of the screen, locate the column that has the leading zeros. You'll likely see a small "Abc" icon above the column name, indicating it's currently a string field.
  3. Click on that "Abc" icon.
  4. From the dropdown menu that appears, select Number (whole) or Number (decimal) as appropriate.

As soon as you make the change, Tableau will convert the field to a numeric data type, and in doing so, it will automatically strip away all the leading zeros. The values '0015' and '09' will instantly become 15 and 9. Voilà!

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

When This Method Fails

This method only works if every single value in the column can be cleanly converted to a number. If your column contains any non-numeric characters, such as in alphanumeric IDs like SKU-00123 or INV0987, attempting this type change will result in those values turning into Nulls because Tableau doesn’t know how to convert "SKU-00123" into a number. If that's your situation, a calculated field is the way to go.

Method 2: Creating a Calculated Field for Text Data

If changing the data type isn’t an option, your next-best tool is a calculated field. This creates a new, corrected version of your column without altering the original data. Here are a couple of functions you can use, ranging from simple to powerfully precise.

Using the INT() or FLOAT() Function

If your string contains only numerals but you still want to use a calculation (perhaps as part of a larger one), the INT() function is your friend. This function takes a string value and converts it into its integer equivalent, dropping leading zeros in the process.

Steps to Create the Calculated Field:

  1. From your worksheet view, right-click anywhere in the Data pane on the left and select Create Calculated Field.
  2. Give your new field a name, like "Product ID (Clean)".
  3. In the formula box, enter the following expression, replacing [Your Field Name] with the actual field from your data source:
INT([Your Field Name])

If your numbers contain decimals, use the FLOAT() function instead, which works nearly the same way but preserves decimal points:

FLOAT([Your Field Name])
  1. Click OK. You now have a new field in your Data pane.

Tableau creates a new measure (indicated by a green '#' icon). You can now use this clean field in your views instead of the original one. If you intend to use this ID for labels or rows (not as a number for aggregation), simply drag the new field to the Dimensions area or right-click it and choose Convert to Dimension.

Limitation: Just like changing the data type, this only works for strings that consist exclusively of numbers. '00456' works perfectly, but 'AZ00456' will return Null.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

The Pro Move: Using Regular Expressions (REGEX)

When you're dealing with mixed alphanumeric data (ID-001, SKU099, Customer007) or you want a solution that will always work regardless of data consistency, Regular Expressions are the most robust and flexible tool for the job.

Don't let the name intimidate you. A "regex" is just a special pattern that we use to find or replace specific text in a string. For removing leading zeros, our pattern is surprisingly simple.

Here, we will use the REGEXP_REPLACE function.

The Failsafe Regex Formula:

Create a new calculated field as before, but this time use the following formula:

REGEXP_REPLACE([Your Field Name], "^0+", "")

Let's break down how this works:

  • REGEXP_REPLACE(...): This is the Tableau function that finds a pattern in a string and replaces it with something else.
  • [Your Field Name]: The dirty field we want to clean.
  • "^0+": This is our search pattern. It's the core of the magic.
  • "": This is the replacement text. By providing two double-quotes with nothing in between, we're telling Tableau to replace what it found (the leading zeros) with nothing... effectively deleting them.

See it in action:

  • 00123 becomes 123
  • 0_EMP-45 becomes _EMP-45
  • ABC-001 remains ABC-001 (correct, the zero is not at the start)
  • ID102030 remains ID102030 (correct, the zero is not at the start)

This calculated field results in a new, clean string field. It is by far the safest and most reliable method because it only targets zeros at the very start of the value and leaves everything else perfectly intact.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Bonus Tip: Clean Your Data Upstream in Tableau Prep

If you're repeatedly cleaning the same data for different workbooks, consider doing it once using Tableau Prep Builder. By cleaning the data before it gets to Tableau Desktop, you save time and ensure consistency across all your reports.

The process is nearly identical:

  1. In Tableau Prep, connect to your data source and add a Clean Step to your flow.
  2. In the Profile pane, click the ellipsis (...) on the column you want to fix and choose an option like Clean > Remove Letters or Remove Punctuation if applicable, or go straight to creating a calculated field.
  3. To create a calculation, click Create Calculated Field from the top bar. You can use the same formulas described above - both INT() and REGEXP_REPLACE() work exactly the same in Tableau Prep.
  4. This will generate a new clean column in your data flow. When you run the flow and output the data source, this clean column will be available for everyone to use in Tableau Desktop, with no further cleaning needed.

Final Thoughts

Removing leading zeros in Tableau is a fundamental step in data cleaning that ensures accuracy and clarity in your dashboards. Whether you use a simple data type change for purely numeric fields or a powerful REGEXP_REPLACE calculation for complex text strings, mastering these techniques will help keep your data reliable and your visualizations sharp.

Manually cleaning data like this is a common task, but it’s often a sign of a larger, more time-consuming problem: messy data sources. We built our product around the idea that you should be able to get from raw data to insights without spending hours wrestling with data prep. In Graphed, we handle the dirty work by connecting directly to your marketing and sales platforms, automating the ingestion, cleaning, and syncing of your data. This means you can just ask a question like "Which ads drove the most revenue last month?" and get an instant dashboard, without ever having to write a regex formula or create a single calculated field.

Related Articles