Split

‹› « »

Split: Split a string into two or more substrings

Sometimes data are provided in a concatenated format, e.g. csv, which needs to be split into separate columns in order to be analysed. Split functions allow you to break apart data using 'delimiter' characters. This is also useful if you need to isolate a specific subset of a string, although Slice can be more useful in this case.

Excel

Select a cell/range of cells > go to the Data tab > Text to Columns > follow instructions in dialogue box. The next time you paste data into a worksheet it will split according to this same logic unless you immediately open the Text Import Wizard (Ctrl-U). If you want to stop this happening automatically you should close and reopen Excel.

NB. A calculation option is available using a combination of LEFT / RIGHT and SEARCH but this only splits into two and takes longer to set up: =~LEFT!~(‹string›,~SEARCH!~(‹delimiter›,‹string›)-1).

Tableau

~SPLIT!~(‹string›, ‹delimiter›«, index»)

SPLIT([Full Name], " ", 1) 
// e.g. "Terence Graham Parry Jones" -> "Terence"

NB.

• The SPLIT function in Tableau only returns one of the results ('tokens'). This is specified by the «index» (referred to in Tableau documentation as 'token_number' but here as 'index' because this is how it behaves, unlike token numbers in the Python example below).

• In Tableau indexing, 1 is the first result, 2 is the second result etc.

• You can count from the end of the string by using a negative «index», e.g.:

SPLIT([Full Name], " ", -2) 
// e.g. "Terence Graham Parry Jones" -> "Parry"

Alteryx

Use the Text to Columns tool (in the Parse palette):

If more complex splitting is needed, you can use the Regex tool (also in the Parse palette):

With the Output Method set to 'Tokenize'. This allows the delimiter to be defined using Regular Expressions (see a summary here).

OrgVue

‹dimension›.value.~split!~("«delimiter»")

node.fullname.value.split(" ") // "Reece Harris" -> "Reece", "Harris"

NB. If you don't provide a delimiter, the default is to split the string into individual characters:

node.fullname.value.split(" ") 
// "Reece Harris" -> R,e,e,c,e,H,a,r,r,i,s

tSQL

~STRING_SPLIT!~(‹string›, ‹delimiter›)

SELECT *
FROM STRING_SPLIT(FullName," ")

Python

‹string›.~split!~('«delimiter», «token_number»')

If no delimiter is supplied, blocks of whitespace are treated as delimiters.

fullName = "Palin   Michael Edward"

fullName.split() 
# Returns ['Palin', 'Michael', 'Edward']

If «token_number» is left blank, the default behaviour is to split the original string into as many substrings as possible.

fullName.split(' ') 
# returns ['Palin', '', '', 'Michael', 'Edward']

«token_number» determines the number of new string segments that are created, and can only be used if a «delimiter» is also supplied.

fullName.split(' ',1) 
# Returns ['Palin', '  Michael Edward']

NB. The above examples assume a variable has been declared for fullName. For more information, go to General > Variables.