How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries

While working with documents in a number of situations, it becomes necessary to change their structure. A popular variant of this procedure is the concatenation of lines. In addition, there is an option to group adjacent rows. In the article, we will consider with the help of what methods it is possible to carry out such types of merging within the Excel program.

Association types

From time to time, a user working in the Excel spreadsheet editor has a need to combine columns in a document. For some, this will be a simple task that can be solved with a single click of the mouse, for others it will become a difficult issue. All methods of combining columns in Excel can be divided into 2 groups, which differ in the principle of implementation. Some involve the use of formatting tools, others use editor functions. When it comes to the simplicity of the task, the undisputed leader will be directly 1 group. However, not in every case, applying the formatting settings, it is possible to achieve the desired results.

Method 1: merging through the format window

Initially, you need to learn how to combine inline elements using the format box. However, before starting the procedure itself, it is required to select adjacent lines planned for merging.

  • To select the lines that need to be combined, it is possible to use 2 tricks. First: hold LMB and draw along the lines – a selection will occur.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
1
  • Second: on this panel, also click LMB on the initial inline element to be merged. Next – on the last line, at this time you need to hold down “Shift”. The entire gap that is located between these 2 sectors is highlighted.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
2
  • When the desired gap is marked, the grouping process can begin. For these purposes, RMB is clicked anywhere in the specified range. A menu appears, followed by the Format Cells section.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
3
  • After that, you need to activate the formatting menu. You need to open the “Alignment” section. Further, in the “Display” a mark is set next to the “Merge Cells” indicator. Then press the “OK” button at the bottom of the window.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
4
  • The marked inline elements are then concatenated. The union of the elements itself will occur throughout the document.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
5

Attention! To achieve the desired result, other methods of switching to the formatting window can be used. For example, after selecting rows, you need to open the “Home” menu, and then click “Format”, located in the “Cells” block. In the pop-up list is “Format Cells …”.

How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
6

In addition, in the “Home” menu, it is possible to click on the slanting arrow located on the ribbon to the right below the “Alignment” section. In such a situation, the transition is made to the “Alignment” block of the formatting window itself. Thanks to this, you do not need to additionally switch between tabs.

How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
7

Also, the transition to a similar window is possible by pressing the combination of hot buttons “Ctrl + 1”, if the required elements are selected. However, in this situation, the transition is made to the “Format Cells” tab that was last visited.

With various other transition options, subsequent operations for grouping inline elements are carried out in accordance with the algorithm described above.

Method 2: Using the Tools on the Ribbon

In addition, it is possible to merge lines using the button on the toolbar.

  • Initially, we select the necessary lines. Next, you need to move to the “Home” menu and click on “Merge and place in the center.” The key is located in the “Alignment” section.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
8
  • When completed, the specified range of lines is concatenated to the end of the document. All information entered in this combined line will be located in the middle.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
9

However, by no means in every case the data should be placed in the middle. To make them have a standard form, the following algorithm is done:

  • The rows to be combined are highlighted. Open the Home tab, click on the triangle located to the right of Merge and Center, select Merge Cells.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
10
  • Ready! The lines are merged into one.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
11

Method 3: joining rows inside a table

However, it is not always necessary to concatenate inline elements across the entire page. Often the procedure is carried out in a specific table array.

  • Highlights the line elements in the document that need to be combined. This can be done in 2 ways. The first one is to hold down the LMB and circle the entire area that needs to be selected with the cursor.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
12
  • The second method will be convenient in the process of combining a significant array of information into 1 line. It is required to click immediately on the initial element of the span to be combined, and then, while holding “Shift”, on the lower right. It is possible to change the order of actions, the effect will be the same.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
13
  • When the selection is made, you should go through one of the above methods to the formatting window. It performs similar actions. The lines within the document are then concatenated. Only the information located at the top left will be saved.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
14

Merging within a document can be done using the tools on the ribbon.

  • The required lines in the document are highlighted by one of the above options. Next, in the “Home” tab, click “Merge and place in the center.”
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
15
  • Or the triangle located to the left of the key is clicked, with a further click on “Merge Cells”.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
16
  • Grouping is carried out in accordance with the type selected by the user.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
17

Method 4: Combining information in rows without losing data

The above grouping methods assume that at the end of the procedure, all information in the processed elements is destroyed, except for those located in the upper left element of the range. However, in some cases it is necessary to group values ​​that are in different elements of the document without loss. This is possible with the extremely handy CONCATENATE function. A similar function is referred to the class of text operators. It is used to group multiple lines into 1 element. The syntax for such a function looks like this: =CONCATENATE(text1,text2,…).

Important! The arguments of the “Text” block are separate text or links to the elements where it is located. The last property is used to implement the problem to be solved. It is possible to use 255 such arguments.

We have a table where a list of computer equipment with a cost is indicated. The task will be to combine all the data in the “Device” column into 1 lossless inline element.

  • We put the cursor anywhere in the document where the result is displayed, and click “Insert Function”.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
18
  • Launch the “Function Wizard”. You need to go to the “Text” block. Then we find and select “CONNECT”, after which we press the “OK” key.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
19
  • The CONCATENATE settings window will appear. By the number of arguments, it is possible to use 255 forms with the name “Text”, however, to solve such a problem, the number of lines that is in the table is required. In a specific situation, there are 6 of them. Set the pointer to “Text1” and, holding the LMB, click the initial element, which contains the name of the product in the “Device” column. The address of the object is then displayed in the box of the window. Similarly, the addresses of the following elements are entered in the fields “Text2” – “Text6”. Further, when the addresses of the objects are displayed in the fields, click the “OK” button.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
20
  • The function displays all the information in 1 line. However, as you can see, there is no gap between the names of various goods, which contradicts the main conditions of the problem. To put a space between the names of different products, select the element that includes the formula, and click “Insert Function”.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
21
  • The arguments window will open. In all frames of the window that appears, in addition to the last one, add: & “”
  • The expression in question acts as a space character for the CONCATENATE function. Therefore, there is no need to enter it in field 6. When the procedure is completed, the “OK” button is pressed.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
22
  • Further, you can notice that all information is placed in 1 line, and is also separated by a space.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
23

There is also another method of combining information from several lines without losing information. For these purposes, you will need to enter the usual formula.

  • We set the symbol “=” to the line where the result is displayed. We click on the initial field in the column. When the address is displayed in the formula bar, we type the following expression: & “” &

Then we click the 2nd element in the column and enter the specified expression again. In a similar way, the remaining cells will be processed, the information in which should be placed in 1 line. In a specific situation, the following expression will be obtained: =A4&” “&A5&” “&A6&” “&A7&” “&A8&” “&A9.

How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
24
  • To display the result on the monitor, press “Enter”.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
25

Method 5: grouping

In addition, it is possible to group lines without losing their structure. Action algorithm.

  • Initially, adjacent rows are selected that need to be combined. It is possible to select separate elements in lines, and not the entire line. Then it is recommended to go to the “Data” section. Click the “Group” button located in the “Structure” block. In the list of 2 positions that appears, select “Group …”.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
26
  • Then you need to open a small window where you select what should be directly grouped: rows or columns. Since you need to group the lines, we put the switch in the required position and click “OK”.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
27
  • When the action is completed, the specified adjacent lines will be grouped. To hide the group, you need to click on the minus icon located on the left side of the coordinate bar.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
28
  • To show the combined lines again, you need to click the “+” symbol that appears where the “-” sign used to be.
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
29

Combining strings with formulas

The Excel editor provides specific formulas to help group information from different rows. The easiest way to use a formula is with the CONCATENATE function. Some examples of using the formula:

Grouping lines and separating the value with a comma:

  1. =CONCATENATE(A1,”, «,A2,», «,A3).
  2. =CONCATENATE(A1;», «;A2;», «;A3).

Grouping strings, leaving spaces between values:

  1. =CONCATENATE(A1,» «,A2,» «,A3).
  2. =CONCATENATE(A1; “;A2;” “;A3).

Grouping inline elements without spaces between values:

  1. =CONCATENATE(A1,A2,A3).
  2. =CONCATENATE(A1;A2;A3).
How to concatenate rows in Excel. Grouping, merging without data loss, merging within table boundaries
30

Important! The main requirement for the construction of the considered formula is that it is required to write down all the elements that should be grouped separated by commas, and then enter the necessary separator between them in quotation marks.

Conclusion

Line grouping methods are selected taking into account what type of grouping is directly needed, and what is planned to be obtained as a result. It is possible to merge lines to the end of the document, within the boundaries of the table, without loss of information using a function or formula, group lines. In addition, there are separate ways to solve this problem, but only user preferences will affect their choice.

Leave a Reply