Ask Your Question

Can I merge many cells at once

asked 2016-09-24 22:55:53 +0200

egpuckett gravatar image

updated 2020-07-10 12:38:40 +0200

Alex Kemp gravatar image

I have 150 names, in two columns: Last Name , First Name. I want them in one column. I can do this with merge cells, but merge will only do one pair at a time. How do I do all 150 names at once, without all of the names winding up in a single cell, which is useless.

edit retag flag offensive close merge delete


Merging cells is mostly evil. Doing it for this purpose is so specifically. Don't think of the solution by @Edward as being a workaround. It is the solution - the Copy / Paste Special omitted..

Lupp gravatar imageLupp ( 2016-09-25 01:21:22 +0200 )edit

Additional remark
Keep also the unconcatenated data!
One day you may want to concatenate them in the revers order? Add a third and fourth part? Prefix a title? ...
Having the data only in concatenated form enhancements may get a nightmare.
(Whether you use the CONCATENATE function or the concatenation operator & is an arbitrary variation. For the concatenation of more than 2 parts of which some may be empty, also the more recently implemented TEXTJOIN may be the tool of choice.)

Lupp gravatar imageLupp ( 2017-09-23 13:04:41 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2016-09-25 07:54:46 +0200

pierre-yves samyn gravatar image

updated 2016-09-25 07:55:49 +0200


Other solution:

  • Copy then EditPaste Unformatted Text in writer
  • Copy then paste from Writer to Calc.


edit flag offensive delete link more

answered 2017-09-23 12:11:39 +0200

dimzev gravatar image

You want merge A and B column to C column go to first cell from C column an type C1=A1&" "&B1 then enter then go to right corner cross , double click and fill all the C column or copy the C1 cell to all ohers Cells you want to merge

edit flag offensive delete link more

answered 2016-09-24 23:54:45 +0200

Edward gravatar image

Possible workaround, you could use the CONCATENATE function =CONCATENATE(A1," ",B1) to report the combined names in Column C (or any other column), and then copy the formula down all 150 rows. You can then copy the results to another sheet or even overwrite back to Column A (use "Paste Special" or "Paste Only" to copy only the results but not the formula).

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-24 22:55:53 +0200

Seen: 3,649 times

Last updated: Sep 23 '17