How to merge cells with text into one without losing data in Excel
The multifunctional spreadsheet editor Microsoft Excel allows you to solve the same task in several ways. The user can perform actions manually, using hot keys, formulas, special add-ons. The same applies to merging cells with different conditions. Today we will talk about how to merge cells with text into one in Excel without losing data. This operation is performed both by regular means of the program, and with the help of third-party plug-ins. If you connect the filled cells in the usual way, then only the contents of the left cell will be saved, so you can’t do without tricks in this case.
Clipboard
Using the normal copy and paste procedure can solve this problem. However, you can successfully transfer the contents of the cells using the built-in Clipboard function. Let's consider the procedure in detail:
- First you need to open a side window with clipboard contents. To do this, in the "Home" tab, click on the button to display this block in the main Excel window.
- Now select the cells with the mouse, the contents of which you want to merge into one. Copy them using the toolbar, context menu, or the keyboard shortcut [knopka]Ctrl[/knopka]+[knopka]C[/knopka].
- Now double-click on the cell where the merged text should fit.
- Click on the object to be pasted from the Clipboard window. After that, the text from the two cells will appear in one cell.
This method is the easiest to implement, but it lacks additional features. You will not be able to select delimiters, apply one algorithm to multiple rows or columns. All actions will have to be performed manually.
Union using a formula
Now let's move on to more complex methods. One of them is the use of a formula. Let's consider the procedure in more detail:
- Double-click the cell where you want to place the combined text.
- Start entering the formula. To do this, put the = sign and click first on the first cell. Then, through the & sign, add the rest of the cells. Apply the formula with [knopka]Enter[/knopka].
- Now you can see the contents of the cells, but without separators. In order for spaces to appear between words or blocks, it is necessary to complicate the formula. It will take the form [k]=D3&” “&E3[/k].
Also Read: Formulas in Excel
You have now merged the text from two cells. But what if you need to repeat the same procedure for several lines in a row? Everything is quite simple. You need to put the mouse cursor in the corner of the new cell and drag it to the end of the entire table.
Expert opinion
Basil
Project manager, comment moderation expert.
Ask a Question So, now when changing the values in the main table, the text will also change in the merged cells. If you do not need this function, then copy the column and paste only its values through the context menu.
Using add-ons
With the help of VBA-Excel, users can significantly expand the basic functionality of the editor. You do not have to figure out the intricacies of creating scripts and macros on your own - just install one of the already created add-ons. For example, install MulTex using this link. After installation, the corresponding tab will immediately appear in Microsoft Excel. With it, you can merge cells in a couple of mouse clicks:
- Open the table and go to the MulTex tab.
- Select an array, row, or column.
- Click the "Cells" button and select the option to merge without losing values.
- In the window that opens, specify the desired parameters and do not forget to select the separator sign.
Many of these add-ons have a free trial period of 30 days. If you wish, you can purchase a permanent license if you absolutely need the VBA-Excel functionality in your work.
Video instruction
The first time to understand the topic of the article is quite difficult, especially without extensive experience with Excel. Therefore, be sure to watch an instructive video on merging table cells.
Conclusion
So, we have considered several ways to combine the contents of several cells into one. Each of them can be used depending on the situation. If you plan to further edit the table, then it is most convenient to use formulas. In other cases, you can get by with the clipboard.