SPLIT
Available as of Datameer v6.3
Syntax
SPLIT(<string>; <string separator>; <index>])
Description
Splits text specified by a string separator(s). An additional index value (with the index of 0 returning the first argument) can be assigned to return a specific split argument.
If you want to split or separate text using regular expression, use the TOKENIZE function.
Examples
Example 1:
Use the SPLIT function to separate a string column. Results are shown in a new column with all separated arguments in individual rows.
Text | Separator |
---|---|
home/user/path1/path2/path3/file_name.csv | / |
Results
Text | SPLIT_Result |
---|---|
home/user/path1/path2/path3/file_name.csv | home |
home/user/path1/path2/path3/file_name.csv | user |
home/user/path1/path2/path3/file_name.csv | path1 |
home/user/path1/path2/path3/file_name.csv | path2 |
home/user/path1/path2/path3/file_name.csv | path3 |
home/user/path1/path2/path3/file_name.csv | file_name.csv |
Example 2:
Assign an index value to specify which arguments are returned.
Text | Separator | Index |
---|---|---|
home/user/path1/path2/path3/file_name.csv | / | 5 |
Results
Text | SPLIT_Result |
---|---|
home/user/path1/path2/path3/file_name.csv | file_name.csv |
Example 3:
If the string starts or ends with a separator, or has two consecutive separators, the SPLIT function produces empty results.
Text | Separator |
---|---|
,,x, | , |
Results
Text | SPLIT_Result |
---|---|
,,x, | |
,,x, | |
,,x, | x |
,,x, |
Example 4:
A limitation with the SPLIT function is that it is not possible to enter whitespaces within the formula builder as a string separator. It is still possible to enter whitespaces manually on the fx line in the workbook.
Example:
Text |
---|
Thomas Jackson |
Emma Smith |
fx = SPLIT(#Text;" ";1)
Result
Text | SPLIT_Result |
---|---|
Thomas Jackson | Jackson |
Emma Smith | Smith |