Was this page helpful?
Yes No

Split a Field into Multiple Fields

If you have string fields in your data that contain multiple units of information, for example, the first and last name of a customer, it might be easier to analyze the data if you split the values in that field into separate fields. You can use split or custom split options in Tableau to separate the values based on a separator or a repeated pattern of values present in each row of the field. In this example, the common separator is a space character ( ).

The new fields created by the split or custom split are added to the data source as calculated fields.

Splits and custom splits are based on the SPLIT string function. For more information, see String Functions.

You can tell if your data supports the SPLIT function by checking the field drop-down menu:

  • On the data source page, check the menu for Split and Custom Split.

  • On the Data pane in the worksheet, check the menu for Transform > Split and Custom Split.

Because different connection types support different functionality in Tableau, you may need to check both places to determine if your data supports the split and custom split options.

Split fields automatically

A string field can be split automatically based on a common separator that Tableau detects in the field.

Depending on the connection type, a split can automatically separate a field’s values in up to ten new fields. The data type of the new fields generated by the split can vary depending on the pattern combination that Tableau detects in the separator that is used to split the original field.

Note: In some cases, if the split takes too long to generate new fields or Tableau cannot find a common separator, a custom split dialog box displays. For more information, see the Custom split section.

To split a field automatically

  1. On the Data Source page, in the grid, click the drop-down arrow next to the field name.

  2. Select Split.

Note: You can also use the split option from the Data pane in the worksheet. In the Data pane, right-click the field you want to split, and then select Transform > Split.

If you do not like the results of the split, you can go to the Data pane and edit the calculated fields that are created by the split. Alternatively, you can click undo in the Tableau Desktop toolbar or remove the split.

To remove fields created by a split

  1. On the Data Source page, in the grid, click the drop-down arrow next to the field name.

  2. Select Delete.

  3. Repeat steps 1-2 to remove all the fields created by the split.

Alternatives to automatic split

Sometimes, using automatic split is not the best option. The following are example of cases when you should not split fields automatically:

  • Values contain different number of separators: Fields cannot be split automatically if the number of separators varies from value to value. For example, suppose a field has the following values:

    jsmith| accounting | north

    dnguyen | humanresources

    lscott | recruiting| west

    karnold |recruiting |west

  • In cases like this, consider using a custom split. For more information, see the Custom split section.

  • Values contain mixed separators: Fields cannot be split automatically if the separator types are different. For example, suppose a field contains the following values:

  • smith.accounting

    dnguyen-humanresources

    lscott_recruiting

    karnold_recruiting

    In cases like this, consider using regular expressions to create new fields. For more information, see Additional Functions.

Custom split

You can use the custom split option to specify a common separator for the split. Like the split option, a custom split can separate a field’s values in up to ten new fields. In addition, you can choose to split the values at the first n occurrences of the separator, the last n occurrences of the separator, or at all occurrences of the separator. The data type of the new fields generated by the custom split always results in a string data type.

To use a custom split

  1. On the Data Source page, in the grid, click the drop-down arrow next to the field name.

  2. Select Custom Split.

Note: You can also access the custom split option from the Data pane. In the Data pane, right-click the field you want to split, and then select Transform > Custom Split.

If you do not like the results of the split, you can go to the Data pane and edit the calculated fields that are created by the split. Alternatively, you can click undo in the toolbar or remove the split.

To remove fields created by a custom split

  1. On the Data Source page, in the grid, click the drop-down arrow next to the filed name.

  2. Select Delete.

  3. Repeat steps 1-2 to remove all the fields created by the custom split.

At a glance: Working with splits and custom splits

The following is a list of additional notes you might need to know about splits and custom splits.

  • New fields generated from a split or custom split cannot be used as keys to join tables, but can be used to blend data sources.

  • New fields generated from a split or custom split cannot be used in a pivot.

  • Split and custom split options are not 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 using a split or custom split on the field that was previously generated by a split or custom split.

Troubleshooting splits and custom splits

The following is a list of 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 or empty cells: After creating a split or a custom split, new fields might contain null values or no values at all. Null values or empty cells occur when there are no values for all of 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 values is used as a separator, those values no longer appear in the new fields. For example, suppose a field contains the following values:

  • ZIP-98102

    ZIP-98109

    ZIP-98119

    ZIP-98195

     

    In this case, the split will create a new field with the following values:

    98102

    98109

    98119

    98195

     

    The split will not create a separate field for “ZIP-“ because the split uses it as a separator.