We use SPLIT () function to split a string field into multiple fields. For example, if your data contains a string field that contains multiple units of information for example the first and last name of a customer – sometimes you can split these multiple units of information into separate fields to help you answer questions requiring one unit of the multiple units.
To split a field into multiple fields, you can use the Split or Custom Split options or write a simple calculated field.
Split or Custom Split option
Using Sample-Superstore data set, you can split the field Customer Name into multiple fields (that is, First Name and Last Name). By opening the drop-down menu and select Split.
Note, using this option Tableau will identify the common separator between the values (in this case a SPACE) and use it to split the values.
Alternatively, you can use Custom Split…. option.
Note this option prompts you to specify the common separator to be used in splitting the fields (that is a SPACE for this case) and the part you’re splitting-off (First, Last or All values).
(In this case, am using a SPACE as the common separator and I will be splitting-off all values)
Using a calculated field to split a field
The syntax for splitting a field looks as follows,
SPLIT (string, delimiter, token number)
String – this is the string field you would like to split in this case ‘Customer Name’.
Delimiter – the part of string to split at (the common separator).
Token number – The part of the string to return, after the delimiter. Note a positive number instructs the function to work from left to right, while a negative number instructs the function to work from right to left.
For example, to split-off the first name of the field Customer Name, I can create the following calculated field.
While to split-off the Last name of the field Customer Name, I can use any of the following calculations. (Assuming every Customer Name contains two names separated by a SPACE).
See the results of the above calculations.
I hope this article was helpful to you. To receive more of the Tableau tips, kindly join our mailing list by subscribing below.
Thanks for reading.