Today has been a tough one for me at the data school. Our first day using Tableau Prep Builder and we learnt heaps with Carl. Here is a cool tip I picked up today when using the SPLIT() function, for when right-click->split doesn’t do you were hoping for and you want a little more control! This tip applies to both Tableau Desktop and Tableau Prep Builder.

REMINDER: SPLIT(string, delimiter, token number)

  • string = the string field you’d like to split
  • delimiter = the part of the string to split at
  • token number = the part of the string to return, after the delimiter. This can be a positive or negative number. A positive number instructs the function to work left to right, a negative number instructs the function to work right to left.

Use Case in Tableau Prep Builder

I have a column called Branch ID containing strings comprised of two parts: a branch code and a branch name. I want to split this at the hyphen that separates them, giving me the 3 character branch code and the branch name in separate columns. Lets use the SPLIT() function to do this.

1. This is what I’m aiming for. 3 columns, [Split – Store Name], [Split – Store Code] and [Branch ID].

Create a calculated field to use the split function for the store name field.

2. To create the [Split – Store Name] field. Use the formula SPLIT([Store ID], “-“, 2).
[Store ID] = Field Name (column header)
“-” = delimiter (split string at the hyphen)
(+)2 = return the second part of the string. (reading left to right)

In the calculated field. The fact that 2 is positive is important because it will return the second part of string reading from left to right

3. Success. A new column has been formed with only our store names

This time lets split using a negative number instead of a positive one. This means we will read from right to left.

4. To create the [Split – Store Code] field. Use the formula SPLIT([Store ID], “-“, -2).
[Store ID] = Field Name (column header)
“-” = delimiter (where to split)
2 = return the second part of the string (reading right to left)

To conclude. +/- signs tell the SPLIT() function which way to read when looking for the the part to split after the delimiter!