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.


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

  1. select both columns, right click, Format Cells, select Text format
  2. right click first column and select Insert Columns Left
  3. insert formula =CONCATENATE(B2,C2) (or whatever cells you need to concatenate) and pull down so the cells will fill with the correspondent concatenation
  4. select the created first column and copy it
  5. 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
  6. 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)
  • 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). Dec 1 '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 2 '16 at 19:28
  • Yess, off course! :) Sep 10 '19 at 8:37

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

  1. Select and copy your columns.
  2. Paste them into a text editor.
  3. Use your editor "search and replace" function to convert tabulations into spaces.
  4. Copy the result.
  5. Paste it into your Gnumeric/LibreOffice/OpenOffice sheet, checking "fixed width".
  • 1
    this was the no-nonse-I-know-this-is-stupid-simple answer I was looking for. Thanks.
    – Kyle Baker
    Mar 8 at 2:37
  • Wow! Unusual way for works! yesterday

Not the answer you're looking for? Browse other questions tagged or ask your own question.