Concatenate rows in Excel

While working with Excel tables, sometimes it becomes necessary to change their structure. One of the most efficient and useful ways to transform table construction is through string concatenation.

It should be noted right away that if 2 or more cells with filled data participate in the merging, then after this operation, only the data of the uppermost left cell will remain in the new merged element, and the rest of the values ​​will be erased. If you need to save all the data, you should use other functions, which will also be discussed below.

So, let’s take a closer look at how you can combine rows in Microsoft Excel.

How to concatenate rows via cell format

The first way is to concatenate strings via cell format. However, before you start directly, the merging procedure itself, you need to make sure that the lines required for merging are highlighted.

1. In order to select the necessary elements, you can go in several ways:
   • in the first case, we move the mouse cursor over the vertical auxiliary area of ​​the program indicating the serial numbers of the lines. After the cursor changes its appearance to a right arrow, hold down the left mouse button and select all the lines that will participate in the union. You can make sure that everything is done correctly as follows – the necessary lines will be visually highlighted.
   • there is another way. In the area of ​​the coordinate panel, after hovering the cursor over the number of the initial line to be merged, click on it with the left mouse button. Next, press the key Shift and click on the last line. This sequence of actions will help you select the area located between the start and end lines, inclusive.
2. When the required area of ​​cells is selected, we proceed to the merge operation. Just right-click on any place in the selected range, in the list that appears, select the item “Format Cells …”.
3. A corresponding window will appear in front of us, in which we go to the “Alignment” tab, check the box next to the “Merge Cells” option and confirm the action by pressing the button OK.
4. At the end of the above steps, all the rows and cells we selected in them were merged into one. Thus, we got one big cell.

Other options for opening cell format

1. Select the desired range, go to the “Home” tab, find the button responsible for the format (located in the group called “Cells”). Click on it and in the drop-down list, select the line “Format Cells …”.
2. Going to the “Home” tab, look at the lower right side of the “Alignment” section. There is a small button with an arrow pointing down and to the right. Clicking on it will open the cell format, and immediately in the tab responsible for alignment, which, although insignificant, saves time.
3. In addition, the program provides the ability to switch to the cell format using hot keys. This case has its own combination “Ctrl + 1”, which can be used after the necessary elements are selected. It is worth noting that this technique throws us into the last visited cell format tab.

Regardless of the method chosen, the further algorithm of actions in order to combine strings is similar to that described earlier.

Concatenating Rows Using Ribbon Buttons

The procedure for merging elements can be performed using the button located directly on the toolbar.

1. First you need to select the desired objects. To do this, you can use one of the methods that were discussed earlier. Next, go to the main tab of the program, where we are interested in the “Merge and Center” button, which is located in the “Alignment” group.
2. By clicking on it, the selected area will be merged into a new element, after which all the values ​​entered into it will be centered.

However, sometimes such centering is not something that does not help, but rather is unnecessary. Therefore, it is necessary to figure out how to place the data in such a way that it corresponds to the standard view or our other wishes.

1. To do this, you need to select the desired area. Now you can go to the main tab, we find the same button that was used in the previous method to combine objects. But now you need to not just click on it, but click on a small triangular icon that opens the list, where we select a simple cell merging.
2. As a result, the rows and cells in them will merge into one new cell, the contents of which will be aligned by default in accordance with the original formatting.

Join Procedure on Table Boundaries

The options discussed above allow you to merge all the cells of the selected rows to the very end of the book. However, in most cases, only a certain part of the table needs to be joined.

Therefore, let’s look at ways that will help to cope with this task.

1. As always, we select all table elements for further manipulations with them. You can perform this action in two ways:
   • In the first case, with the left mouse button held down, circle the entire range of elements that will participate in the merge.
   • The second option is perfect for combining a large array of objects into one block. It is required to click on the upper left cell of the area to be merged, then hold down Shift and click on the lower right cell. It is allowed to perform this action in the reverse order, that is, first select the lower right, and then the upper left cell and
2. The next step after the selection is to open the cell format with any of the methods that we discussed in the first part of the article. Choose the one you like best and implement it.
3. Note: Only the data that is located in the top left cell of the selection area will be saved, which the program will warn us about.

It is possible to produce union within a table using the buttons on the toolbar.

1. We select the range using one of the two methods described in the previous examples. In the “Home” tab, click either on the merge and simultaneous center alignment button, or click on the triangular icon, which is located on the side of the button, and select the “Merge cells” item in the drop-down list.
2. The merge procedure will be performed based on the type selected by the user.

How to merge without losing data

The options for merging table elements described earlier involve deleting all the data in the cells, with the exception of one. The only thing that remains after this procedure is the value located in the upper left cell. However, in some cases it is necessary that after processing all the information from the cells participating in the merging process is preserved. Excel developers have foreseen this and provided the user with a special concatenation function, with which cells can be merged without losing data.

The main task of the function is to combine a range of rows containing data into a single element. Here is what the formula of the tool that allows you to do this looks like: =CONCATENATE(text_1, text_2,…)

The function argument can be either a specific value or the coordinates of a cell in a table. We will use the last option, since it will help us solve the problem correctly. The maximum number of function arguments is 255, which is more than enough.

For example, let’s take the same table with the name of the goods. Now it is necessary to merge all the data located in the first column in such a way that nothing is lost.

1. We set the cursor in the cell in which the result will be displayed after the procedure. Then click on the button that is responsible for inserting the function.
2. After the Function Wizard appears on the screen and select the line “CONNECT” from the list, then click OK.
3. Now we need to fill in the function arguments. As already known, their maximum number is 255, however, in our case, only 5 will be enough (the number of rows that are planned to be combined). In the function arguments management window that appears, click on the first field, then on the first cell of the row that is included in the range we have selected. If everything is done correctly, then the coordinates of the selected cell will appear in the first field, and its value next to it. Next, you need to repeat the procedure with all the other cells participating in the merger. After that, press the “OK” button.
4. After damage, all the values ​​of the selected cells will be inserted into the specified cell in one line, without hyphens and spaces. This, of course, looks ugly, so you need to fix everything. To do this, select the cell in which the formula is located and click on the “Insert Function” button again.
5. The Argument Settings window will reappear on the screen. In each of the fields after the cell address you need to add special characters &” “. But, in the last field, only the address should be indicated. These characters are a kind of space character when using the concatenation function. It is for this reason that they are not needed in the last field. After everything is done, confirm the action by pressing the OK button.
6. Now spaces are inserted between the values, as we wanted.

There is another method of combining elements while preserving their values. In order to put it into practice, there is no need to apply the chaining function, it will be enough to use the usual formula.

1. First you need to write an equal sign (“=”) in the cell in which we plan to display the final result. Next, click on the first cell located in the column from the selected range. Its address should be highlighted in the formula bar, as well as in the cell in which we write the formula. Now write special characters &” “&.
2. After that, in the same way, add the rest of the cells from the rows of the column selected for merging to the formula. The result should be the following formula: A2&” “&A3&” “&A4&” “&A5&” “&A6&”.
3. In order to display the result, it remains only to press the Enter key. After that, you can see that using this formula, we have achieved exactly the same result as in the case of using the linkage function.

Row grouping

In addition to these actions, you can still use row grouping, while maintaining the structural integrity of the table. Let’s see how this can be done.

1. It is necessary to select the elements participating in the grouping. In this case, you can specify not only ranges of rows, but also individual elements. Then go to the “Data” tab and press the “Group” button, which is located in the “Structure” block.
2. In the window that opens, select the grouping option. In our case, we need to group rows, so we put a mark on the appropriate option and confirm the action.
3. After that, the selected rows will be combined into a group. To hide it, you need to click on the icon, which shows a “minus”. It is located on the left side of the coordinate bar.
4. If you need to expand the range of grouped elements, then you just need to click on the plus sign (+), which is displayed in the same place where the minus used to be (in case the range of rows was previously collapsed).

Conclusion

Thus, depending on the structure of the Excel table and the tasks set, you can use different options for concatenating rows. The program provides the ability to merge elements, both in a wide range and with certain restrictions, with and without data loss, using functions and formulas, as well as using a tool such as grouping. Also, in certain cases, you can combine these methods to achieve the best result.