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!
Hello,
don’t fiddle around with merging, if you want a fast and simple method:
Asumptions (for the answer)
A: CityB: StateC: ZIP Code2
D
Action Now do the following:
=TEXTJOIN(", ";1;A2:C2) into cell D2 (combines 3 values delimited by ,<space>)Data -> Calculate -> Formula to ValueA, B and C
Hope that helps.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
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.
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
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().
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.