Merge Neighbouring Columns

Your data keeps the surname and the first name in two columns, but the table should show one "Name". The mergecells function joins a column with the columns to its right, row by row, with the separator of your choice between the values.

What mergecells does

Give it a starting column, how many columns to its right to take in, and a separator. In every row, the values of those columns are joined into the starting column, and the columns that were merged in are removed. The columns after them move left.

The same attendee list as in the previous tutorial, but with the names already in two columns, merged with the arguments 1^1^, (start at column 1, take in 1 more column, 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

The header row is merged like the others, which here gives the sensible heading "Surname, First name" for free.

Setting it up

In step 5 (Modifications) of the Table Wizard, click Add new Data Modification, set Function Type to Built-in/Existing one, choose mergecells as the Function Name and type the arguments in Function Args. Leave Table Element and Element Range at Table and all, and check the result in step 6.

A Data Modification rule in step 5 of the Table Wizard with Function Name mergecells, its description, the arguments 1^1^, and the argument format [startColumn]^[additionalColumns]^[separator].

The arguments

[startColumn]^[additionalColumns]^[separator]

Part	Required	Default	Meaning
startColumn	Yes	-	The column the merged values go into, counting from 1.
additionalColumns	No	1	How many columns to the right to merge in. 1 means two columns in total, 2 means three.
separator	No	a space	The text placed between the values. Kept exactly as typed, spaces and HTML included.

Examples

Arguments	Merges	"Papadopoulos" + "Maria" becomes
`1`	columns 1 and 2, with the defaults	Papadopoulos Maria
`1^1^,`	columns 1 and 2, comma and space	Papadopoulos, Maria
`1^1^`	columns 1 and 2, no separator at all	PapadopoulosMaria
`2^1^ -`	columns 2 and 3 (First name and City)	Maria - Athens
`1^^<br/>`	columns 1 and 2 (default count), one per line	Papadopoulos Maria

Leaving the separator out is not the same as leaving it empty. 1^1 uses the default space; 1^1^, with the caret but nothing after it, joins the values with no separator. To keep the default count but choose a separator, leave the count empty: 1^^ / .

Things to know

Why did nothing happen?

The table is left as it was when the starting column or the count is not a whole number of at least 1, or when the merge would run past the last column - for example 3^2 on a four-column table, which would need columns 3, 4 and 5.

The merged heading reads oddly.

The header row is merged too, so "First name" and "City" become "First name - City". Give the column a proper heading with a replacement rule limited to row 1 - see Replace Values Based on Criteria.

What about empty cells?

The separator is still added. A missing first name gives "Okafor, " with the trailing comma; clean it up with a replacement rule if your data has gaps.

Is HTML in the cells kept?

Yes. The values are joined as they are, so a link in one of the merged cells is still a link afterwards.

What happens to the column numbers?

The merged columns disappear, and every column after them moves left. Rules that run after the function see the new numbers: in the first example, City moves from column 3 to column 2.

Can I merge rows instead?

Yes, with mergerows, the subject of the next tutorial. To do the opposite and split a column, use splitcolumns.

Try it on your own data

Open the Table Wizard, load a table in step 1 and add a mergecells modification in step 5.

Open the Table Wizard