0

I have several columns that I wish to put into one. Right now I am stuck with copying and pasting them into one column, is there a quicker way of doing this?

Any help hugely appreciated

Gerry Tierney
  • 7
  • 2
  • Are there different numbers of cells in each column, or the same? Are there any spaces between cells? – Tom Sharpe Jan 18 '21 at 14:56
  • 1
    Does this answer your question? [Excel - Combine multiple columns into one column](https://stackoverflow.com/questions/2977660/excel-combine-multiple-columns-into-one-column) – Dave Jan 18 '21 at 14:58
  • Concatenating means taking the string values from each column and combining them. It seems like this is what you want. It may be a good idea to edit the question and provide an example. Also, are you using LibreOffice or Apache OpenOffice? The title says "openOffice" but that name is out of date and was never formally correct - see https://en.wikipedia.org/wiki/OpenOffice.org#Naming. – Jim K Jan 18 '21 at 17:49

1 Answers1

0

If you have excel, you could use VBA.

  1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, and paste the following macro in the Module Window.

.

Sub CombineColumns()
    Dim xRng As Range
    Dim i As Integer
    Dim xLastRow As Integer
    Dim xTxt As String
    On Error Resume Next
    xTxt = Application.ActiveWindow.RangeSelection.Address
    Set xRng = Application.InputBox("please select the data range", "Select range", xTxt, , , , , 8)
    If xRng Is Nothing Then Exit Sub
    xLastRow = xRng.Columns(1).Rows.Count + 1
    For i = 2 To xRng.Columns.Count
        Range(xRng.Cells(1, i), xRng.Cells(xRng.Columns(i).Rows.Count, i)).Cut
        ActiveSheet.Paste Destination:=xRng.Cells(xLastRow, 1)
        xLastRow = xLastRow + xRng.Columns(i).Rows.Count
    Next
End Sub

  1. Then press F5 key to run this code, and a prompt box will pop out to remind you select the data range that you want to combine into only one column.

  2. And then click OK button, and the selected columns have been merged into only one column.

Tips: After running this VBA, the original data of the range will be cleared, you’d better copy and save them in another location first.

Dave
  • 249
  • 1
  • 11