SPLIT
- Juliane Wetzel
Available as of Datameer X 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 |