Ask Your Question

3 columns, 100 rows, can I merge columns and still keep the rows? Simply!

asked 2020-01-23 20:56:25 +0100

cardguy gravatar image

updated 2020-07-21 23:18:22 +0100

Alex Kemp gravatar image

3 columns - city, state, zip. 100 rows of addresses. What is the simplest way )if possible) to merge the 3 columns, such that individual rows are still respected, but now with city state and zip combined/merged into 1 cell for each address.

Result: 1 column, 100 rows.

I've looked online, tried different schemes to no avail or success. This would seem like a textbook example of what should be easy and convenient, but for some reason is completely missing.

Help! Please!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-01-23 22:50:27 +0100

Opaque gravatar image

updated 2020-01-23 22:56:59 +0100


don't fiddle around with merging, if you want a fast and simple method:

Asumptions (for the answer)

  • Column A: City
  • Column B: State
  • Column C: ZIP Code
  • Starting Row: 2
  • Combined column: D

Action Now do the following:

  • Add formula: =TEXTJOIN(", ";1;A2:C2) into cell D2 (combines 3 values delimited by ,<space>)
  • Drag down formula 100 rows
  • Data -> Calculate -> Formula to Value
  • Optional Delete columns A, B and C

Hope that helps.

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

edit flag offensive delete link more


Worked like a champ! But... Does seem like a "button + drag" or "select + drag + Rt click" (opens options dialog asking if you want to merge) would be a wonderful and intuitive addition. Just sayin.

cardguy gravatar imagecardguy ( 2020-01-24 01:48:59 +0100 )edit

I don't agree, merging cells isn't normally not what you want when using spreadsheets (IMO the need to merge in general is an indication of poor design) . But anyway - If you have an enhancement request - file it here

Opaque gravatar imageOpaque ( 2020-01-24 12:35:56 +0100 )edit

Merging definitely is the wrong way in such a case.
Also after having created "compound addresses" the deletion of the original columns would be a grave mistake. Never destroy your original data! You may need them for a different purpose next time, and getting them back from compound representations mostly is complicated and error-prone. There isn't even a function allowing to split again a compound made by TEXTJOIN().

Lupp gravatar imageLupp ( 2020-01-24 20:02:21 +0100 )edit

answered 2020-01-24 17:38:43 +0100

cardguy gravatar image

I didn't fully illustrate my specific use/need to merge these columns, and perhaps I'm an outlier. But my issue may be a good example of other uses for spreadsheets, not anticipated by the developers.

I'm not using Calc as a spreadsheet per se. I'm using it as a design tool to do variable printing in Adobe Indesign. Most of the variable printing software wants to pull City, State, Zip from 1 cell, due do a specific peculiarity of zip codes starting with zero. Despite asking for, and illustrating how we need addresses formatted to print customer's envelopes, we continue to receive multi-column versions from those same customers, and hence the need to combine those three columns. Until now we've done it by hand, and these can be hundreds of addresses/cells. So your formula is a god send - thank you, but my suggestion is perhaps like adding a "claw" to a hammer. Perhaps not first anticipated, but pretty useful once it became standard.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-01-23 20:56:25 +0100

Seen: 69 times

Last updated: Jan 24 '20