Description
Split a column into multiple new columns or rows.
Examples
Split the 'Courses' column into multiple columns or rows:
Split the 'URL' column into multiple columns using colon, slash, question mark or ampersand:
Split characters 4 to 13 and 15 onward into new columns:
Inputs
One.
Options
•Select the Column you wish to split.
•Set Using depending on whether you wish to split using a Text delimiter (usually a single character), a Regex delimiter or Lengths of each column.
•Set the Delimiter for splitting the column. E.g. , (comma). For Text delimiter or Regex delimiter only.
•Set Lengths as a comma separated list of the number of characters in each new column. Put - in front of a value to ignore it. Use * as a wildcard to mean 'the rest of the value'. Only 1 * can be used. For Lengths only. For example:
o3,5,4 will create 3 columns with characters 1-3, 4-8 and 8-11
o-3,5,-4,* will create 2 columns with characters 4-8 and 12 onward
o-*,3 will create 1 column with the last 3 characters
o2,-*,2 will create 2 columns with the first 2 and last 2 characters
•Set Into to:
oColumns to create a new column for each split value.
oRows to create a new row for each split value (the original row is removed).
•Set Ordering depending on how you want to order values after splitting.
•Check keep empty if you wish to keep empty values (e.g. honor delimiters with nothing in between). For Text delimiter or Regex delimiter only.
•set Min. values to the minimum number of columns/rows you wish to split each value into. For Text delimiter or Regex delimiter only.
•set Max. values to the maximum number of columns/rows you wish to split each value into (ignored if less than minimum). For Text delimiter or Regex delimiter only.
Notes
•Set Min. values to the same as Max. values to always create the same number of columns.
•To split a column by carriage returns or tabs set Using to Regex delimiter and set Delimiter to \n or \t, respectively.
•If Into is Columns, new columns are added at the right end. You can change the column order with Reorder Cols.
•If there is a header, the header of the new column is based on the original header. You can change the column name with Rename Cols.
•The opposite of Split Col with Into as Columns is Concat Cols.
•The opposite of Split Col with Into as Rows is Unique.
See also