Interesting

How to combine cells in Excel (easy ways)

Good time!

Working with Excel is still somewhat different from other programs: it is not so easy to take and combine several cells into one (which is very necessary when editing some documents). Sometimes, novice users are confused by this ...

In general, most of the typical tasks in Excel are solved using simple formulas that are not difficult to understand. And in this post I will try to give the most illustrative examples of combining cells (I will consider two options: when the cells are empty, and when they already have data).

Note: I want to note right away that the methods and examples described below are relevant for modern versions of Excel 2013, 2016, 2019.

By the way, for those who do not have a modern version of Excel - I can recommend using its online version: //ocomp.info/chem-zamenit-word-i-excel.html

*

Ways to combine cells in Excel

If the cells are empty

Also, these methods will be relevant if you do not need the data in the merged cells.

Method 1

And so, in order to quickly and easily combine any number of cells, you need:

  • select the necessary ones with the mouse (in my example, I selected A1, B1, C1 and D1);
  • In chapter "The main" click on an instrument "Merge Cells" , see screenshot below.

Select and Merge Cells (Excel 2019)

At the same second, the cells will become merged (note: by the way, if you had any data in them, then all this will be deleted, except for the data from cell A1).

Mission accomplished

Please note that cells can be merged not only horizontally, but also vertically. This is done in the same way!

Vertical merge

Method 2

If you do not find the required button (option) on the Excel toolbar, you can perform this task differently:

  • first also select the required cells;
  • then click on the selected area with the right mouse button and in the menu that appears, open the item "Cell format" .

Cell format

Next, go to the "Align" tab and check the box next to the function "Merging cells" ... Click OK.

Alignment tab

As you can see in the screenshot below, the problem has been successfully solved (and this method is quite working, however, it forces you to make 1-2 "extra" mouse clicks).

Task Completed - Cells Merged

If there is data in the cells (which also needs to be combined)

An important note: when merging cells, we will not summarize their contents, but "glue" (concatenate) together. Those. if you have a first name in one cell, and a surname in another - after the union there will be one line with the first and last name; if in one cell the number "5", and in the other "3" - after the union will be "53" (and not as with the sum: "8").

Method 1

As an example, I took a list of the names of several fictional characters. The task, by the way, is quite close to life (I had to deal with this).

Initial data

First, you need to select one of the cells opposite the first line (I took E1) and write the following expression in the formula bar:

= CONCATENATE (A1; B1; C1)

We write specials. formula (CONCATENATE)

After you hit Enter, the formula will concatenate all the listed cells in it. In my case, it turned out the full name, but without spaces (which is convenient).

The cells are linked

To fix this, just change the partial formula (add quotation marks with a space, as in my example):

= CONCATENATE (A1; ""; B1; ""; C1)

If you need spaces (or other characters) between the combined data, adjust the formula

Of course, instead of a space in quotes, you can put any other character (a hyphen, for example).

After that, it remains only to stretch the formula to other lines, and the problem will be solved ...

Stretching the formula to other lines

How to transfer glued data to another document (so that it does not depend on formulas)

Our concatenated lines in this document depend on formulas, and if you change them slightly, then everything will "corrode".In some cases, it is much more convenient to work not with them, but with text strings. Let's try to transfer our results to another document (or a new sheet).

And so, first you need to select our concatenated cells (in my example, this is the column "E"), right-click on them and select the function "Copy" .

Copy

Next, open a new document (or sheet), right-click on the desired cell and click on the menu "Paste special" ... See example below.

Specialist. insert

In the settings window that appears, indicate that you only need to insert "Values" (not the formulas themselves) and click OK.

The values

As you can see in the screenshot below, text values ​​from previously linked cells were inserted into the document (and not the formulas themselves).

No formulas (only text strings)

Method 2

You can combine cells in Excel without specials. formulas, just use the "&" sign.

The formula in this case will look like this:

= A1 & B1 & C1

Another option for combining cells with content

Of course, to add spaces between the combined values, you need to slightly change the formula:

= A1 & "" & B1 & "" & C1

Insert spaces between data

If you want hyphens instead of spaces, the formula changes slightly:

= A1 & "-" & B1 & "-" & C1

What the formula will look like if we insert hyphens instead of spaces

When the "adjustment" of the formula is completed - just drag the corner with the mouse and drag it to other rows (columns). Then Excel will apply it to other cells (example below).

The formula is extended to other cells

As for the transfer of the results to another document, this is done in the same way as the above ...

*

I will round off on this! Additions on the topic are welcome.

Happy work ...

👌

$config[zx-auto] not found$config[zx-overlay] not found