Split One Column into Several
Split One Column into Several
"Papadopoulos, Maria" in one cell, when you want the surname and the first name in two. "120x60x75" when you want width, depth and height side by side. The splitcolumns function cuts a column at a separator you choose and spreads the pieces over new columns.
What splitcolumns does
Give it a column and a separator. In every row, the text of that column is cut wherever the separator appears: the first piece stays in the column, and each further piece goes into a new column inserted immediately to its right. The columns that were already to the right move along to make room.
An attendee list whose first column holds "Surname, First name", split with the arguments 1^, (column 1, separator comma-and-space):
| Before: Surname, First name | City | Ticket |
|---|---|---|
| Papadopoulos, Maria | Athens | Workshop |
| Smith, John | London | Conference |
| Rossi, Giulia | Milan | Workshop |
| After: Surname | First name | City | Ticket |
|---|---|---|---|
| Papadopoulos | Maria | Athens | Workshop |
| Smith | John | London | Conference |
| Rossi | Giulia | Milan | Workshop |
Notice the header. The header row is split like every other row, so a heading written in the same "Surname, First name" form becomes two headings by itself.
Setting it up
Add a Data Modification
In step 5 (Modifications) of the Table Wizard, under Data Modification, click Add new Data Modification.
Pick the function
Set Function Type to Built-in/Existing one and choose splitcolumns as the Function Name. The wizard shows its description and argument format.
Type the arguments
In Function Args, type the column, a caret, and the separator: 1^, . Leave Table Element and Element Range at Table and all.
Check the preview
Step 6 (Preview) shows the split table.
![A Data Modification rule in step 5 of the Table Wizard with Function Type Built-in/Existing one, Function Name splitcolumns, its description, the arguments 1^, and the argument format [columnID]^[separator]^[maxColumns].](/tabulizer7/images/tutorials/data-modifications/splitcolumns-function.png)
The arguments
[columnID]^[separator]^[maxColumns]
| Part | Required | Meaning |
|---|---|---|
| columnID | Yes | The column to split, counting from 1 at the left. |
| separator | Yes | The text to cut at. It is matched literally, character for character, including spaces: , (comma and space) is not the same separator as ,. |
| maxColumns | No | The most columns the split may produce. Leave it out for no limit. |
Examples
A product list whose second column is "Size (cm)":
| Arguments | Desk: 120x60x75 | Shelf: 80x30 | Lamp: 45 |
|---|---|---|---|
2^x |
120 | 60 | 75 | 80 | 30 | (empty) | 45 | (empty) | (empty) |
2^x^2 |
120 | 60x75 | 80 | 30 | 45 | (empty) |
Every row gets the same number of columns: as many as the row that split into the most pieces, up to the limit. Rows with fewer pieces are filled out with empty cells, so the table stays rectangular.
Things to know
Why did nothing happen?
The table is left exactly as it was when the arguments are not valid (a column number past the last column, a missing separator, a limit that is not a whole number), when the separator appears in no cell of the column, or when the limit is 1. Check the arguments against the format above.
Does it split the header too?
Yes, every row including row 1. A header without the separator keeps its text in the first column and gets empty cells after it; give those columns a heading with a replacement rule on row 1, or write the header in the split form.
What happens to the other columns?
Columns to the right of the split column move right by the number of new columns. Rules that run after the function - removals, replacements, formats, styles - use the new numbers. In the example above, City moves from column 2 to column 3.
What about separators at the start or end, or two in a row?
Each produces an empty piece. "a,,b" split at a comma gives "a", an empty cell and "b".
Can the separator contain a caret?
No. The caret (^) separates the arguments, so it cannot be part of the separator.
What is the opposite?
mergecells, which joins neighbouring columns into one. It has its own tutorial in this series.
Try it on your own data
Open the Table Wizard, load a table with a column to split in step 1, and add a splitcolumns modification in step 5.