How to merge two columns

English
#1

I have about 200 rows I’m trying to merge together. I’ve tried various solutions such as this, and this, and none of them seem to work.

I’ve tried selecting all columns, copying and pasting into Writer, and doing find and replace to replace tabs with spaces, but that doesn’t find any tabs when I use the regex \t. I also tried converting the columns to merge to text, adding a new column, clicking on the column label and adding =CONCATENATE(E1:E128,F1:F128). It just copies the first row, and does not fill in all subsequent rows.

This seems like an operation that should involve selecting the target columns, and clicking a button for merging all rows on a 1 to 1 basis directly. Is there a simple means of just merging two columns into one column?

#2

You could enter your formula, but as you want an array formula, you would have to press Ctrl+Shift+Enter after editing the formula. You would then see {=CONCATENATE(E1:E128;F1:F128)} in your formula bar

This will not leave any space between the data so if you are joining first name and last name you would add a space, so instead enter =CONCATENATE(E1:E128;" ";F1:F128)
.
You cannot edit just part of an array formula. Sometimes it is simpler to to just enter in cell G1 =E1&F1 and the drag down or copy and paste.

Optional:

  1. Select the range in column G and click Data > Calculate > Formula to value to convert the formula to static data
  2. Delete columns E and F

BTW This merging data, not columns

#3

How do you merge all rows contained in columns on a 1 to 1 basis?

so for instance E1 merges with F2, E2 merges with F2 and so on.
I believe Excel has a feature for doing that.

#4

Did you try it?

#5

I tried your example, and it gives an error.

I tried the =E1&F1 formula, and it only fills the first row. When I drag down nothing else fills. It just adds those rows to the formula.

#6

Sorry, I was using 25.2 for the array formula works in 24.8.4.2 and back to at least 6.4.7.2
.
Copy and Paste

  1. Copy the cell G1
  2. Select the range G1:G128
  3. Click Paste

Drag

  1. Press Enter to exit Edit mode
  2. Hover over the bottom right corner of cell G1 until the cursor turns into a cross
  3. Click on the corner and drag down to G128
1 Like
#7

Ok, I found out what I was doing wrong. I was putting my cursor in the formula bar, selecting the cell down, and then dragging down. I needed to click the bottom right hand corner to drag down. I was trying to do it from the center bottom. Is there a hot key for switching between selection modes?

#8

You have to press Enter to exit editing before trying drag down

#9

Yeah, the color needs to be pink. If I try to drag down from the blue selection highlight, after pressing enter, at the bottom it just selects all rows for being able to paste in. But, not with the pink selector that applies formula to all of the rows.

#10

This works, but how the company’s AI told me to format the data to import it with their API was wrong.

#11

You might find the section on speeding up data entry in Chapter 3 of the Getting Started Guide useful.
The Calc Guide expands on Calc’s abilities. Both can be downloaded from English documentation | LibreOffice Documentation - LibreOffice User Guides

#12
  1. And double-click on the corner.
3 Likes
#13

I keep forgetting about the double-click; too set in my ways I suppose.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.