Split a Field into Multiple Fields
If you have string fields in your data that contain multiple distinct pieces of information (for example, the first and last name of a customer) you may be able to split the values into separate fields.
You can use split options in Tableau Desktop to separate the values based on a separator (delimiter) or a repeated pattern of values present in each row. In the example of Customer Name, the common separator is a space (" ") between first and last name.
Find the Split option
Tableau Desktop – but not web editing in the browser – has a menu option for automatic or custom splits. These are based on the SPLIT string function, which can also be used manually in a calculation for complete control over the split.
Not all data sources support SPLIT. You can tell if your data supports the SPLIT function by checking for the Split and Custom Split menu options:
On the Data Source page, check the menu for Split and Custom Split.
From the Data pane on a sheet, check the menu for Transform > Split and Custom Split.
Split on the web
Web authoring doesn't support splitting from a menu. To split data while working in the browser, you can manually create a SPLIT calculation. See Alternatives to splitting from the menu.
Split fields
A string field can be split automatically based on a common separator that Tableau detects in the field. You can also manually define the split
Depending on the connection type, a split can automatically separate a field's values in up to ten new fields.
Split a field automatically
On the Data Source page, in the grid, click on the drop-down arrow next to the field name, then select Split.
In the Data pane, right-click the field you want to split, and then select Transform > Split.
The results of the split are standard calculated fields that can be edited or deleted like any other calculated field. The data type of the new fields generated by the split can vary depending on the pattern combination that Tableau detects.
Split a field with custom split
Use the custom split option for more control over the split. You can specify the separator and choose to split the values at the first N occurrences of the separator, the last N occurrences or at all occurrences.
On the Data Source page, in the grid, click on the drop-down arrow next to the field name. Select Custom Split.
From the Data pane, in the Data pane, right-click on the field you want to split, and then select Transform > Custom Split.
The new fields generated by the custom split always result in a string data type.
Note: In some cases, if the automatic split takes too long to generate new fields or Tableau can't find a common separator, the custom split dialog box displays.
Custom split is useful when the number of separators varies from value to value. Automatic split can't be used in these cases.
For example, suppose a field Employee Info has the following values:
- jsmith| accounting | north
- dnguyen | humanresources
- lscott | recruiting| west
- karnold |recruiting |west
The custom split could be configured to split on the pipe character ("|") and split off all columns. For the value without a region, the third result field will be null for that row.
Alternatives to splitting from the menu
Split manually using the SPLIT function
The SPLIT function works similarly to a custom split, but only one result field is returned per calculation. You define the separator and specify which value to return by providing a token number.
For example, to split Customer Name into First Name and Last Name, create two calculated fields:
- First Name:
SPLIT([Customer Name], " ", 1) - Last Name:
SPLIT([Customer Name], " ", 2)
The Employee Info example (such as "jsmith| accounting | north") would use three calculated fields:
- Username:
SPLIT([Employee Info], |, 1) - Department:
SPLIT([Employee Info], |, 2) - Region:
SPLIT([Employee Info], |, 3)
For more information, see the SPLIT function.
Split manually using LEFT and RIGHT functions
If your use case is more to extract a specific part of a string than to split it based on a separator, you can use the LEFT and RIGHT functions. These return only a portion of the string based on a specified number of characters.
For example, if the value of a row is IGW8892:
| Calculation | Result |
RIGHT([Value], 4) | 8892 |
LEFT([Value], 4) | IGW8 |
LEFT(RIGHT([Value], 2) , 1) | 9 |
For more information, see LEFT and RIGHT in String Functions.
Use regular expressions for fields containing mixed separators
Fields can’t be split automatically if the separator types are different. For example, suppose a field includes the following values:
- smith.accounting
- dnguyen-humanresources
- lscott_recruiting
- karnold_recruiting
In cases like this, consider using regular expressions to extract the desired information. For more information, see Additional Functions.
Working with splits and custom splits
Things to keep in mind when working with splits and custom splits.
New fields can’t be used as keys to join tables but can be used to blend data sources.
New fields can’t be used in a pivot.
Split and custom split options aren’t supported for sets, groups, parameters and bins.
Microsoft SQL Server only allows up to four split fields.
To generate more than ten new fields, consider splitting multiple times. You can use split again on a field from the first split that still includes multiple pieces of information.
Troubleshooting splits and custom splits
Known issues you might experience when using splits and custom splits:
Split and custom split options missing for a supported data source type: Split and custom split options are available only for fields that are a string data type.
Null values: After creating a split or a custom split, new fields might contain null values or no values at all. Null values or empty cells happen when there are no values for the expected new fields.
Data has been removed: Tableau might use portions of the field’s values as a separator. If a portion of a field's value is used as a separator, those values no longer appear in the new fields.
For example:
| Original value | Split field values |
| Zip-98102 | 98102 |
| Zip-98109 | 98109 |
| Zip-98119 | 98199 |
| Zip-98195 | 98195 |
The split doesn't create a separate field for "Zip-" because that entire string is used as the separator.