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.

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

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

This time lets split using a negative number instead of a positive one. This means we will read from 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!