There are several ways to merge cells in excel, using formula or using no formula, losing data or losing no data. Which solution is right for you, please read the article below.
The Merge Cells menu is in the “Home” tab and “Alignment” group. There are several options to choose from.
Merge & Center
Merge the selected cells into a single combined cell and make a center alignment.
Merge the selected cells into a combined cell for the same row
Merge Across is a Merge Cell for several rows in one command, there is no difference in results between Merge Across and Merge Cell if the selected cells consist of one row
Merge selected cells into a single combined cell without alignment.
Unmerge Cells returns the merged cells into the original form.
Unmerge Cells only returns the cell to its original form, not the data.
You can merge cells with the right-click menu, then select Format cell, a dialog appears as shown below.
Check the Merge Cells check box in the Text control section. The other setup is optional. You can make text alignment or text orientation settings, all in one dialog.
If you only check the Merge cells checkbox, the results are like the Merge Cells option on the Ribbon menu.
=A2&" "&B2&" "&C2
The formulas merge the data in several columns into one column without losing data. The formula merges the data, not the cells.
=CONCATENATE(A2," ",B2," ",C2)
The Formula writing for the CONCATENATE function and the ampersand sign requires you to write all the columns to be merged, along with the separator.
If there are many columns to be merged will create a long formula. For a simpler solution, use the TEXTJOIN function.
The TEXTJOIN function allows you to merge cells using a range, without having to write one by one of the cells to be merged. Also, you only write the separator once, unlike the CONCATENATE function you must write the separator repeatedly.
No formula, losing no data
Solution #1 and #2 only merge the cells, not the data inside. As a result, you will lose some data.
Solution #3 and #4 are using the formula, losing no data because the merged cell is done by putting it in a new column. The disadvantage, you must write all the column addresses to be merged one by one as well as the separator.
Solution #5 using a formula, you only write the column range address and the separator once. But my choice is solution #6, simple, no formula, losing no data and magic 😊.
What about your choice?