We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Trouble merging cells - all end up in one line

asked 2019-12-04 21:03:54 +0200

kasloman gravatar image

updated 2019-12-04 21:07:25 +0200



As shown above I am trying to merge cells so that the results are : same number of rows with the * attached to the left side of the field in each row.

As shown below what I get is the last row containing all fields merged properly but in one long string.


To get what I want I would have to merge each row of two columns. Is there a setting I don't know about??

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-12-04 23:16:19 +0200

Opaque gravatar image

updated 2019-12-04 23:18:04 +0200


you get what you requested. You selected range A3:B11 and requested 'em to be merged. And that's what you get. If you want it to be row by row you need to do it row by row. Thus:

  • Select A3:B3 and merge
  • Select A4:B4 and merge
  • and so on

Or you do the following (avoiding to merge)

  • Select cell C3
  • Add the formula: =CONCATENATE(A3;" ";B3) into C3
  • Drag the formula down to C11
  • Select Range C3:C11 and Data -> Calculate -> Formula to Value
  • Delete columns A and B (optional)

Hope that helps.

If the answer is correct or helped you to solve your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more

answered 2019-12-04 23:06:59 +0200

Lupp gravatar image

Merging is evil. If you insist to merge cells in a range row by row you will need user code. In LibreOffice Basic:

Sub mergeRowByRow(Optional pSheetCellRange as Object, Optional pSeparator As String)
If IsMissing(pSheetCellRange) Then pSheetCellRange = ThisComponent.CurrentSelection
If IsMissing(pSeparator)      Then pSeparator = ""
If NOT pSheetCellRange.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Sub
uR            = pSheetCellRange.Rows.Count    - 1
uC            = pSheetCellRange.Columns.Count - 1
For j = 0 To uR
 j_Lead        = pSheetCellRange.getCellbyPosition(0, j)
 j_Rg          = pSheetCellRange.getCellRangeByPosition(0, j, uC, j)
 j_DA          = j_Rg.getDataArray
 newString     = Join(j_DA(0), pSeparator)
 j_Lead.String = newString
Next j
End Sub

Did I tell you that merging is evil?

edit flag offensive delete link more


Boy, I'll take the CONCATENATE(). I don't know where that code comes from but much too complicated.

Thanks, R

kasloman gravatar imagekasloman ( 2019-12-06 02:23:33 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-12-04 21:03:54 +0200

Seen: 333 times

Last updated: Dec 04 '19