CONCATENATE function in Excel. How to concatenate cell contents in Excel using CONCATENATE

There is a special function CONCATENATE in the spreadsheet processor, which implements the union of the contents of 2 or more cells. The ability to use this operator allows you to efficiently and quickly work on large amounts of data in tabular form. Let’s take a closer look at the functionality of the CONCATENATE operator.

Description and syntax of the CONCATENATE function

Starting from 2016, this function was renamed in the spreadsheet and became known as “SCEP”. Users who are used to the original name can continue to use “CONCATENATE” as the program recognizes them the same way. General view of the operator: =SCEP(text1;text2;…) or =CONCATENATE(text1,text2,…).

Important! 255 is the maximum possible number of function arguments. Larger quantities are not possible. Attempting to implement more arguments will result in an error.

Inserting and Setting a Function

Experienced spreadsheet users know that by merging several cells into one, the data of all components is erased, except for the top leftmost one. The CONCATENATE function prevents this. The walkthrough looks like this:

1. We select the sector in which we want to carry out the merging process. Select it and go to the “Insert Function” element.

2. A small window called “Insert Function” was displayed on the screen. Expand the list next to “Categories:” and click “Text”. Next, select “SCEP” and click on the “OK” button.

3. A new window appeared, designed to specify the arguments of the function. Here you can enter both specific indicators and cell references. Addresses can be entered independently by manual entry or by simply clicking on the cells on the worksheet.
4. We move to the line “Text1” and click on sector A2.

5. We move to the line “Text2”, enter “, ” (comma and space) there to separate the arguments.

6. We move to the line “Text3” and click on sector B2.

7. In the same way, we fill in the remaining arguments, and then click “OK”. In the lower area of ​​the window you can see the preliminary result.

8. The implementation of the merging of all selected sectors into one was successful.

9. There is no need to repeat similar manipulations for the sectors of the column remaining below. You just need to move the mouse cursor over the lower right corner of the sector with the displayed result. The pointer will take the form of a small plus sign. Hold LMB and drag the plus sign down to the very bottom line of the column.

10. As a result, we got a filled column with new data.

This was the most standard way to use the CONCATENATE function. Next, we will consider in more detail various methods of linking sectors and dividing indicators among themselves.

How to use the CONCATENATE function in Excel

Let’s analyze in as much detail as possible five ways to use the CONCATENATE function in a spreadsheet.

Method 1: Combine data in cells

Data Merge Step by Step Guide:

1. We make a selection of the cell in which we want to display the combined values. We click on the “Insert function” element, located next to the line for entering formulas.

2. The Function Wizard window appears on the screen. Select the “Text” category, and then find the “CONCATENATE” function. After completing all the manipulations, click on “OK”.

3. The familiar argument window was displayed on the screen. We install the pointer in the first line of the window. Next, on the worksheet, select the link containing the data needed for the merging. We perform similar actions with the 2nd line, highlighting another sector. We perform this maneuver until the addresses of all sectors are entered in the arguments box. After completing all the steps, click “OK”.

4. As a result, the data of the selected sectors were displayed in one pre-selected sector. The main disadvantage of this method is that all data is displayed together, without any separators. It will not work to add separators on your own, without changing the formula.

Method 2: Applying a function with a space

This shortcoming is easily fixed by adding spaces between the function arguments. The walkthrough looks like this:

1. We implement the actions described in the algorithm presented above.
2. We double-click LMB on the sector with the formula to allow its change.

3. Insert spaces between values ​​in quotation marks. Each such expression must end with a semicolon. The result should be the following expression: “”;

4. Press the “Enter” key on the keyboard.
5. Ready! Gaps appeared between the values, and the displayed information began to look much nicer.

Method 3: Adding a Space Via the Arguments Window

The above method is only suitable in cases where there is not a lot of data. If you implement such a separation method with large amounts of information, then you can lose a lot of time. The following method allows you to space spaces as quickly as possible using the arguments window. The walkthrough looks like this:

1. We find any empty sector on the worksheet and double-click on it with LMB, enter a space inside it. It is better that the sector is located further from the main plate. The selected cell should never be filled with any information.

2. We implement the algorithm of actions from the previous methods to get to the function arguments window. As in the previous methods, we enter the value of the first sector with data in the first field. Next, point to the second line and indicate the address of the sector into which we just entered a space. To significantly speed up the procedure, you can copy the sector value using the “Ctrl + C” combination.

3. Next, enter the address of the next sector. In the next field, add the address of the empty sector again. We repeat similar actions until the data in the table runs out. After carrying out all the manipulations, click the “OK” button.

As a result, we got a combined record, the data in which is separated by a space.

Method 4: Merging columns

The CONCATENATE operator allows you to combine the values ​​of several columns into one. The walkthrough looks like this:

1. With the sectors of the first line of the combined columns, we implement the same manipulations that are shown in the 2nd and 3rd examples. It is worth mentioning that if you want to use the method with an empty sector, then you need to make an absolute type reference for it. To do this, precede all coordinate symbols with a “$” sign. Other fields remain relative. After carrying out all the manipulations, click on the element “OK”.

2. Hover over the lower right corner of the sector with the formula. After the pointer takes the form of a plus sign, by holding the left mouse button we stretch the marker to the very bottom of the table.

3. After the implementation of this process, the information indicated in the columns will be combined into one column.

Method 5: Adding More Characters

The CONCATENATE operator is used to enter additional expressions and characters that were not in the original concatenation area. It is worth noting that thanks to this operator, you can embed other functions of the spreadsheet processor. The step-by-step tutorial looks like this:

1. We implement manipulations to add values ​​to the arguments window from the methods described above. In any of the fields we insert arbitrary textual information. Text material must be surrounded by quotation marks on both sides.
2. After completing all the steps, click “OK”.

3. As a result, in the selected sector, along with the combined data, the entered textual information appeared.

Inverse CONCATENATE function in Excel

There are several operators that allow you to split the values ​​of one cell. Function examples:

1. LEFT. Outputs the specified part of the characters from the beginning of the line. Approximate view: =LEVSIMV(A1;7), where 7 is the number of characters to extract from the string.

2. RIGHT. Outputs the specified part of the characters from the end of the string. Approximate view: =RIGHTSIMV(A1;7), where 7 is the number of characters to extract from the string.

3. PSTR. Displays the specified portion of characters, starting at the specified position. Approximate view: =PSTR(A1;2;3), where 2 is the position from which the extraction starts, and 3 is the number of characters to be extracted from the string.

Function editing

It happens that the operator has already been added, but some changes need to be made to it. This can be done in two ways. First option:

1. Select the cell with the finished function and click on the “Insert Function” element, located next to the line for entering formulas.

2. A familiar window for entering operator arguments appeared. Here you can make all the necessary changes. Finally, click on “OK”.

The second option:

1. Double-click on the sector with the formula and go to change mode.

2. We are adjusting the values ​​in the sector itself.

Regardless of the option used, when editing manually, you must be as careful as possible in order to avoid mistakes.

Pay attention! Sector coordinates must be entered without quotes, and arguments must be listed separated by semicolons.

CONCATENATE function for a large number of cells

When working with a large number of cells, an array of data is specified as a reference. The walkthrough looks like this:

1. Let’s imagine that our data is located in one line (the fifth in a row).
2. Enter the entire range to merge into the empty sector and add a space through the ampersand sign.

3. Press the “F9” key. The formula outputs the result of the calculation.
4. A space was added to all the words, and a “;” was formed between them. We get rid of unnecessary brackets and insert this array into the formula.

5. After carrying out all the manipulations, press the “Enter” key

Connecting text and date

Using the CONCATENATE function, you can combine text information with a date. The walkthrough looks like this:

1. For correct merging, you first need to enter the date in the TEXT operator. The operator allows you to format a number.
2. DD.MM.YY value. determines how the date will look. For example, if you replace YY with YYYY, the year will be displayed as four digits instead of two.

It is worth noting that you can add text information to numeric information not only using the CONCATENATE operator, but also using a custom number format.

Function operation video

If the above instructions are not enough to understand how the CONCATENATE function works, then we recommend that you check out the following videos that tell you how to merge cells correctly without losing information:

After watching the video instructions, you will clearly see how this function works using examples, learn about the various nuances of using the operator and supplement your own knowledge about it.

Conclusion

The CONCATENATE function is a useful spreadsheet tool that allows you to merge sectors without losing data. The ability to use the operator will help users significantly save time when working with large amounts of information.