Awọn akoonu
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.
- 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.
- 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.
- 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.
- The marked inline elements are then concatenated. The union of the elements itself will occur throughout the document.
Ifarabalẹ! 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 …”.
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.
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.
Ọna 2: Lilo Awọn irinṣẹ lori 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.
- 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.
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.
- Ready! The lines are merged into one.
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.
- 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.
- 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.
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.”
- Or the triangle located to the left of the key is clicked, with a further click on “Merge Cells”.
- Grouping is carried out in accordance with the type selected by the user.
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,…).
Pataki! 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”.
- 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.
- 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.
- 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”.
- 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.
- Further, you can notice that all information is placed in 1 line, and is also separated by a space.
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.
- To display the result on the monitor, press “Enter”.
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 …”.
- 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”.
- 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.
- To show the combined lines again, you need to click the “+” symbol that appears where the “-” sign used to be.
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:
- =CONCATENATE(A1,”, «,A2,», «,A3).
- =CONCATENATE(A1;», «;A2;», «;A3).
Grouping strings, leaving spaces between values:
- =CONCATENATE(A1,» «,A2,» «,A3).
- =CONCATENATE(A1; “;A2;” “;A3).
Grouping inline elements without spaces between values:
- =CONCATENATE(A1,A2,A3).
- =CONCATENATE(A1;A2;A3).
Pataki! 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.
ipari
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.