Splitting and de-limiting columns in Tableau can quickly go from very fast and easy with the click of a few buttons in the data source editor to much more complicated requiring the use of various formulas to parse out the exact part of a text string that you need. We’ll cover the basics as well as highlight some advanced techniques for splitting columns in Tableau.
The difficulty level of splitting columns in Tableau will largely depend on your starting data. Common scenarios like splitting columns based on a single hyphen or setting up a de-limit by comma are relatively quick but parsing without a de-limiter requires the use of formulas.
Let’s jump in!
Table of Contents
When to Split and De-Limit Columns in Tableau
Data used for reporting and developing dashboards comes in all shapes and sizes. One of the most common necessities is to split a column that’s previously been concatenated to extract or work with key information. This process is quite simple when a de-limiter exists, such as a comma or a hyphen. It becomes more complicated when there is no clear de-limiter and requires the use of formulas.
Some common scenarios that require splitting text into multiple columns includes.
- Re-creating Date Formats – It’s not uncommon to see dates formatted like June-2023 where the data needs to be split into pieces for further formulas to be applied to apply time intelligence functions.
- Parsing File Names – When a date key or categorical information does not exist in a dataset sometimes it is easier to place it into a file when saving it and extract the data as a column for analysis later.
- Splitting Full Names into First and Last – Splitting names based on a space can be tricky when people have multiple words in their name and require advanced parsing techniques.
We wanted to highlight these before we started to show that when parsing columns, it’s important to keep in mind the context and being creative in how you approach splitting text can be very beneficial.
Now let’s look at some examples.
Splitting and Delimiting Columns in Tableau
The fastest way to split columns in Tableau is from the Data Source tab. Right click a column header and select “Split”. Tableau automatically detects concatenated columns and splits the data into multiple new columns. You can select “Custom Split” which gives you the ability to define specific de-limiters and number of columns.
More advanced options include using Formulas or Regular Expressions. We’ll look at each one of these different possibilities.
Option 1.) Let Tableau Auto-Split a Column
From the Data Source tab in Tableau Desktop, navigate to the data preview panel at the bottom of the page. Right click on a column header and select “Split”. This option will auto-detect the most common delimiters and create new columns for each instance of the de-limiter.
In the example below, we split a column with a single de-limiter and on the right side of the results preview are the two new columns. Each new column contains the pre-fix of the original column name.
This method is the fastest, but gives you the least amount of control of how the column is split.
Option 2.) Manually Split a Column with the Custom Split Feature
From the Data Source view, right click on a column header of a column you want to split and select “Custom Split” a window will appear that allows you to define the delimiter or separator. The box contains notes of common separators, but you can override them with any value that you want to.
The second setting asks how many columns should the split result in. For example, setting it to Split off the First 1 Column will generate a single column with the values to the left of the first instance of the delimiter. Other options include Last N Columns or All Columns.
Option 3.) Create a New Calculated Field to Extract Column Data with Formulas
To extract or split text into a new column in Tableau using formulas, start by creating a calculated field. You can start one either by right clicking on a column name in the Data Source view or you can create a new formula directly in the visualization builder.
We recommend using the Data Source view so you can preview the results after completing the formulas.
The formulas used will be very familiar to you if you are experienced using Microsoft Excel. Tableau supports formulas for, LEFT, RIGHT, and MID.
Each one of these extracts a different part of the column based on the number of characters in the column.
In the example below, we created a RIGHT formula that extracts the first 7 characters from the right side of the Category column.
The three formulas are as follows.
- RIGHT([Field Name],# of Characters)
- LEFT([Field Name],# of Characters)
- MID([Field Name],Start Character,# of Characters)
As you begin typing in the calculated field formula box, Tableau will prompt you with what the next section of the formula is separated by a comma.
Option 4.) Regular Expressions (Regex)
Regular Expressions are a subset of the Perl programming language used to identify parts of text based on text patterns. They are incredibly useful and powerful but not well understood by many Tableau developers. We recommend learning at least the basics of Regex because they allow you to perform advanced parsing that would otherwise be impossible or very difficult using standard delimiting methods.
In the example below, we created a calculated field in Tableau and used the RegEx extract formula to extract any and all characters from a column up to the point where there is a – symbol.
You can also use these methods to replace specific characters or sets of characters and parse based off of longer text patterns than a single delimiter.
When parsing complex text strings, we recommend using online tools like RegEx101 that will highlight text that would be parsed by formula for a live preview of what your formula will do. You can also use AI tools like OpenAI’s ChatGPT to generate Regex formulas for you.
Tableau Data Type Requirements for Splitting Columns
Regardless of which option you choose to split a single column into multiple columns in Tableau, the column must be defined as a string or text field. Tableau will be unable to apply parsing formulas to numeric fields. To check or change the data type of a column, you can look at the symbol at the top of each column in the Data Source view, and click it to adjust the data type.
This is the most common cause of not being able to split, parse or delimit a column. Make sure that it’s defined as a String data type prior to applying a formula or parsing operation.
De-limiting CSV Files imported into Tableau
One of the most common scenarios for needing to de-limit a dataset in Tableau comes when working with text or .csv files. These file formats are often concatenated with commas. If you need to split a csv file into multiple columns, rather than using the standard text to columns features in Tableau, import the dataset, and from the Data Sources section click on the table name, and select “Text File Properties”
This menu will give you the ability to quickly change the field separator and adjust whether or not text should be designated with quotes, double quotes etc. You can even define custom characters for the field separator.
Tableau also offers an excellent help guide on how to work with text files and csv.
Conclusion
Mastering the art of splitting and delimiting columns in Tableau can significantly enhance your data transformation and data prep capabilities. While basic splits are straightforward, the real power lies in creating calculated fields and applying custom formulas or Regex for advanced scenarios.
It used to take hours to learn Regex and even longer to become proficient in it, but advanced AI tools can quickly generate complex formulas for you in seconds allowing you to quickly split columns even when a clear delimiter isn’t available for one of the more common methods.