How to merge cells in Openoffice

Q I have two sets of data in an Openoffice Calc spreadsheet in two columns. I would like to combine these in column C.

I am only interested in the data not the formulas that produced it. I want to copy the data elsewhere, in the same page , or in another sheet, or transfer it to another program. I don’t want the underlying formulas.

Bob Wishart

A There are a couple of concepts here with spreadsheets, neither of which is difficult to overcome.
The first is combining the two cells. We are assuming from what you say that they are text rather than numbers to be added or subtracted.

To combine the text in cells A4 and B4 use the following formula =CONCATENATE(A4;B4). You may want to add a space between the cells, in which case the formula should be written as =CONCATENATE(A4;" ";B4).

There is a similar command in Microsoft Excel, but it is written slightly differently. For the same example the formula would be =CONCATENATE(A4," ",B4). You will notice that the only real difference is that a comma is used to separate the different bits of information.

The only problem with adding a space between the cells is if there is already a space at the end of the first cell. This is easy to solve with the trim function that is available in both Calc and Excel. The final formulas for Calc and Excel are =TRIM(CONCATENATE(A6;" ";B6)) and =TRIM(CONCATENATE(A6," ",B6)) respectively.

The second part of the question is about copying the combined text as a value rather than a formula. As far as Excel or Open Office are concerned, there’s no need to do this as they will work the same with the contents of the cell whether it is a formula or a direct entry. It also helps you to update the spreadsheet.

If you need to change a name in a list, all the names will be updated automatically. If the chain of the formulas is broken, you would have to do this updating by hand, which takes more time and adds the potential for mistakes.

If you are sure you want to break the links of formulas, this is done by copying the range of information and then using a special Paste command. In Excel, select the range of cells and press Ctrl and C together to copy it to the clipboard.

Left-click on the cell you want to start the copied range from, click on the Edit menu and then Paste Special. Click in the circle labelled Values, then on OK.

Openoffice works a little differently. Select and copy the cells in the same way and then click on the Edit menu and Paste Special. Click in the box labelled Paste all so that the tick disappears and the other options below will become available.

Click in the Formula box so this tick disappears as well, then click on OK. The result of the formula will then be pasted in the cell.

Baffled by jargon? See our free online jargon buster.