How to merge 2 columns into one in LibreOffice or OpenOffice?

Question

In my CSV, I have two columns which are really just one column, so I want to concatenate the content of these two columns - for each row - into the first one(after this I want to remove the extra column)? I use Mac, so I use LibreOffice and OpenOffice.

score 20 · Accepted Answer · answered Dec 01 '16 at 18:25

20

I like to complicate things so here what I would do:

select both columns, right click, Format Cells, select Text format
right click first column and select Insert Columns Left
insert formula =CONCATENATE(B2,C2) (or whatever cells you need to concatenate) and pull down so the cells will fill with the correspondent concatenation
select the created first column and copy it
right click first column, select Paste Special and select Text under Selection, None under Operations and Don't shift under Shift Cells (remove all other selections). If warning appears just click Yes for overwriting the current cells
Now we have a column with the concatenated texts, so you can delete columns B and C (or whichever you have concatenated) and this will leave the concatenated text in first column (no formula there)

answered Dec 01 '16 at 18:25

Zina

2,247
2
14
12

Thanks. Paste special was the thing I was missing. In the meanwhile I did the first part (steps 1, 2, 3, 4). If you just do paste, the formula stays, and if you delete the two initial columns, LibreOffice is confused (puts #REF or something, as a replacement for the result of the operation). – Vladimir Despotovic Dec 01 '16 at 21:56
1

Confused :) Good one. It is not confused - it is just telling you that you are pointing to some non-existent content as you deleted the referenced values. – Zina Dec 02 '16 at 19:28
Yess, off course! :) – Vladimir Despotovic Sep 10 '19 at 08:37

score 13 · Answer 2 · answered Jun 12 '20 at 15:38

13

The "standard" solution seems too much of a trouble for such a simple thing, so here is what I'll use :

Select and copy your columns.
Paste them into a text editor.
Use your editor "search and replace" function to convert tabulations into spaces.
Copy the result.
Paste it into your Gnumeric/LibreOffice/OpenOffice sheet, checking "fixed width".

answered Jun 12 '20 at 15:38

Skippy le Grand Gourou

2,085
1
21
19

4

this was the no-nonse-I-know-this-is-stupid-simple answer I was looking for. Thanks. – Kyle Baker Mar 08 '21 at 02:37
2

Wow! Unusual way for works! – Tuhin Mitra Dec 07 '21 at 11:57
1

This is the most beautiful and useful comments I ever seen in this site in years! – luca76 Mar 03 '22 at 10:06

score 1 · Answer 3 · answered Apr 07 '23 at 10:14

1

I did this like following way In the column where you want to get the merged values write following.

=A2 & " " & B2

and drag to the end of column.

Update: added screenshot

Click on the cell, then hold from the right corner and drag.

answered Apr 07 '23 at 10:14

Mukesh

131
1
6

How to merge 2 columns into one in LibreOffice or OpenOffice?

3 Answers3