How to merge cells in Excel without data loss?

Description of the problem

Unfortunately, when you combine two or more cells containing text, Excel retains the value only the first cells. The data in the remaining cells will be deleted. You can solve this problem in a standard way by first combining the text of the cells using the function COUPLING ... However, this is somewhat inconvenient and time-consuming. To quickly and conveniently combine cells and DO NOT lose data in them, it is better to use the add-on to Excel - the add-in VBA-Excel ... This is clearly illustrated in the figure on the right.

The program allows you to combine cells while maintaining data as a separately selected range, and to combine cells row by row or by columns.

COMBINE

The procedure for these cases is detailed below.

If you need to merge one area of ​​cells

  1. Select the range of cells you want to merge while keeping the text.
  2. Go to the tab VBA-Excel
  3. Click on the command Merge cells , combines text from multiple ranges and / or strings by inserting a user-specified separator between the text values.
  4. Please select separator in the dialog box that appears. For convenience, there is a preview of the result. COMBINE (delimiter; ignore_blanks; text1; [text2]; ...)
  5. Click the button To combine

If you need to combine multiple ranges of cells at once

WhereThe VBA-Excel add-in can simplify the process even if you need to concatenate a large number of cells while preserving the data (for example, by rows or columns).

To do this, you need to perform the first 4 steps from the instructions above and additionally set the type of merging of cells.

the character to be inserted between fragments is indicated in quotes;

Save settings function

All selected options are saved automatically and will be restored the next time this function is run. This is to save time when combining a large number of ranges. You can also run this function by hotkey. How to do this, see the article Creating a hotkey for a command.

3 ways to glue text from multiple cells

The inscription on the fence: “Katya + Misha + Semyon + Yura + Dmitry Vasilyevich +

comrade Nikitin + red-haired plumber + Vitenka + telemaster Zhora +

bastard Redulov + do not remember the name, such a long-haired + 19 more men + husband = love! " Method 1. Functions CONNECT, CONNECT and COMBINE In the category Text there is a function

ignore_blanks

COUPLING

(CONCATENATE) In the category , which connects the contents of multiple cells (up to 255) into a single whole, allowing them to be combined with free text. For example, like this: Nuance: do not forget about spaces between words - they must be written as separate arguments and enclosed in brackets, because the text. Obviously, if you need to collect many fragments, then using this function is no longer very convenient, since you will have to write links to each cell-fragment separately. Therefore, starting with the 2016 version of Excel, to replace the function its more perfect version came with a similar name and the same syntax - function

- if 0, then empty cells are included, if 1 - ignored

CONCEPT (CONCAT) ... Its fundamental difference is that now, as arguments, you can specify not single cells, but entire ranges - the text from all cells of all ranges will be combined into one whole: For bulk merging, it is also convenient to use the new function

COMBINE (TEXTJOIN) ; which has been introduced since Excel 2016. It has the following syntax: ; = COMBINE ( ; Separator Skip_or_Empty_cells

Range1

  • Range2 ...)
  • Where
  • Separator - the character to be inserted between the fragments

The second argument is responsible for whether to ignore empty cells (TRUE or FALSE)

Text

Range 1, 2, 3

... - ranges of cells, the contents of which we want to glue

For instance: Method 2. Symbol for pasting text (&) This is a versatile and compact linking method that works in absolutely all versions of Excel. +For summation the contents of multiple cells use the plus sign " &", and for

  • gluing
  • cell contents use the "

"(located on the number" 7 "on most keyboards). When using it, remember that:

... is a range of cells, the contents of which should be glued.

This symbol must be placed at each connection point, i.e. at all "junctions" of text lines, just like you put a few pluses when adding several numbers (2 + 8 + 6 + 4 + 8) If you need to paste arbitrary text (even if it's just a period or a space, not to mention the whole word), then this text must be enclosed in quotation marks. In the previous example with the CONCATENATE function, Excel takes care of the quotes itself - in the same case, they must be entered manually. For example, here is how you can collect your full name in one cell of three with the addition of spaces: If combined with the function of extracting the first letters from the text -

COMBINE

LEVSIMV

(LEFT) , then you can get a surname with initials with one formula: Method 3. Macro for combining cells without losing text. We have text in several cells and the desire is to combine these cells into one, merging their text there. The problem is one thing - the button

Merge and center (Merge and Center) in Excel, he knows how to combine cells, but with text the complexity - only the text from the upper left cell remains alive. To merge cells with merging text (as in Word tables), you will have to use a macro. To do this, open the Visual Basic Editor on the tab Developer - Visual basic +(Developer - Visual Basic) or a keyboard shortcut Alt F11

, let's insert a new program module into our book (menu 

Insert - Module Visual basic +) and copy the text of such a simple macro there: Sub MergeToOneCell () Const sDELIM As String = "" 'delimiter character Dim rCell As Range Dim sMergeStr As String If TypeName (Selection) <> "Range" Then Exit Sub 'if no cells are selected - exit

With Selection

For Each rCell In .Cells

sMergeStr = sMergeStr & sDELIM & rCell.Text 'collect text from cells

Next rCell

Application.DisplayAlerts = False 'disable the standard text loss warning

.Merge Across: = False 'merge cells

Application.DisplayAlerts = True .Item (1) .Value = Mid (sMergeStr, 1 + Len (sDELIM)) 'add the total text to the merge cell End With

  • End Sub
  • Now, if you select several cells and run this macro using the keyboard shortcut F8 or by button

Macros

on the Developer tab

(Developer - Macros)

, then Excel will combine the selected cells into one, merging the text there, separated by spaces. Related links

The tutorial describes various methods to quickly merge two or more cells in Excel without losing data. In Excel spreadsheets, you may often need to join two or more cells into one large cell. For example, this can be useful for better presentation of data. In other cases, there may be too much content to display in one cell of the table, and you decide to join it with adjacent empty cells.

Merge cells Whatever the reason, merging cells in Excel is not as easy as it sounds. If at least two of them you are trying to merge contain data, then the standard merge function will only store the top left value and destroy the rest of the data. But is there a way to merge cells in Excel without data loss? Of course have. Merge and center function. The fastest and easiest way to concatenate two or more cells in Excel is to use the built-in option “ Combine and

center". The whole process takes only 2 quick steps:

Select the range you want to combine.

On the " The main" + 1.

click

In this example, we have a list of fruits in A2, and we want to piece it together with a couple of empty cells on the right (B2 and C2) to create one big one that will fit the entire list.

  • After you click the button, A2, B2 and C2 will turn into one and the text will be centered like in this screenshot: Other methods. To access a few more integration options provided by Excel, click the small drop-down arrow next to the button
  • and select the option you want from the drop-down menu: Concatenate by lines - connect the selected cells in each row separately: - merge selected into one without centering the text. The text will be left aligned by default, and the number will be right aligned. Note. To change the alignment of the text after the merge, simply select the alignment you want in the "
  • the main ".  > And another good way to merge cells is through the context menu. Select the appropriate area with the cursor, right-click, and then select "Format Cells" from the list. In the window that appears, select the "Align" item and put a check mark next to "Merge cells". Here you can also set other parameters: word wrap, auto-fit, horizontal and vertical text orientation, various alignment options. After everything is set, click "OK".

I think you remember that the formatting window can also be invoked with the keyboard shortcut

CTRL

Limitations and features.

There are a few things to keep in mind when using Excel's built-in functions to merge cells:

  • Make sure that all the data you want to see is entered in the last
  • left cell
  • the selected range. After all, only the contents of the upper left cell will be saved, the data in all the rest will be deleted. If you want to integrate two or more cells with data in them together, see the next section just below. The fastest and easiest way to concatenate two or more cells in Excel is to use the built-in option “ If the button is inactive, most likely the program is in the editing
  • ... Click on

Enter key,

to finish editing and then try merging.

  • None of the standard join options work for data in an Excel spreadsheet. First, you need to convert the table to a regular range. To do this, right-click the table and select from the context menu
  • Table
  • Convert to Range

... And then follow the instructions.

How to merge cells in Excel without data loss

As mentioned, the standard merge functions only save the contents of the top-left position. While Microsoft has made quite a few improvements to the latest versions of the program, the merge feature seems to have escaped their attention. And this critical limitation persists even in Excel 2016 and 2019. Well, where there is no obvious way, there is a workaround :)

Method 1. Combine cells in one column ("Align" function)

It is a quick and easy connection method without losing information. However, this requires that all the data to be joined is in the same area in the same column.

Select all the cells of the table that you want to combine.

  • Make the column wide enough to contain all the content. The main" + COn the tab “
  • "Use" Fill in" > Align>. This will move all content to the very top of the range.
  • Choose an alignment style depending on whether you want the final text to be centered or not.
  • If the resulting values ​​are spread across two or more rows, make the column slightly wider. .

This merge method is easy to use, however it has a number of limitations:

You can only combine in one column.

It only works for text, numeric values ​​or formulas cannot be handled this way.

This does not work if there are empty cells between the merged cells.

Method 2. Use the CONCATENATE function

Users who are more comfortable with Excel formulas may like this way of combining cells. You can use the CONCATENATE function or the & operator to first concatenate the values ​​and then concatenate the cells as needed.

  1. Let's say you want to connect A2 and B2. There is data both there and there. To avoid losing information during the merge, you can use any of the following expressions: D= CONCATENATE (A2; ""; B2) = A2 & "" & B2 We will write the formula in D2. And now we have as many as 3 positions: two initial and one combined. Next, a few additional steps are required: Copy D2 to clipboard (you can use Combine and
  2. ). Paste the copied value at the top left position of the range you want to concatenate (in A2). To do this, right-click it and select "

Paste Special> Values  и in the context menu. Now you can delete the contents of B2 and D2 - we will no longer need it, it will only interfere.

Highlight the positions you want to connect (A2 and B2) and then -

  1. You can connect multiple cells in the same way. Only the CONCATENATE formula will be slightly longer in this case. The advantage of this approach is that you can use different delimiters in the same expression, for example: Copy D2 to clipboard (you can use
  2. = CONCATENATE (A2, ":"; B2; ","; C2)
  1. You can find more examples in this tutorial - CONCATENATE in Excel: How to Concatenate Text Rows, Cells and Columns. How can I concatenate columns without losing data? + Suppose you have customer information where the first column contains the last name and the second column contains the first name. You want to combine them so that the last name and first name are written together. + We use the same approach using formulas that we just discussed above.
  2. Insert a new column into your table. Place your mouse pointer on the column header (in our case, this is How can I concatenate columns without losing data? + C), then right-click and select " How can I concatenate columns without losing data? + Paste »From the context menu. We will only call it “
  3. Full name How can I concatenate columns without losing data? In D2, write the following: How can I concatenate columns without losing data? + = CONCATENATE (B2; ""; C2) B2 How can I concatenate columns without losing data? + Suppose you have customer information where the first column contains the last name and the second column contains the first name. You want to combine them so that the last name and first name are written together. + C2

- this is the surname and first name, respectively. Note that there is still a space added between the quotes. This is the delimiter that will be inserted between the concatenated names. You can use any other character as a separator, such as a comma.

Likewise, you can merge data from multiple cells together into one using any separator of your choice. For example, you can combine addresses from three columns (street, city, postal code) into one. Copy the formula to all other column positions

We have combined first and last names, but this is still a formula. If we delete the Name and / or Surname, the corresponding linked data will also disappear.

Therefore, we need to convert it to a value so that we can remove the unnecessary from our sheet. Select all the filled cells in our new column (select the first one and apply the keyboard shortcut Ctrl .

Shift

  1. ). Suppose you have customer information where the first column contains the last name and the second column contains the first name. You want to combine them so that the last name and first name are written together. + Copy the selection to the clipboard ( or Ins How can I concatenate columns without losing data? + Suppose you have customer information where the first column contains the last name and the second column contains the first name. You want to combine them so that the last name and first name are written together. + whichever you prefer), then right-click anywhere in the same snippet (Full Name) and select Paste Special from the context menu. Select the "Values" radio button and click the "OK" button.
  2. Delete the First Name and Last Name columns that you no longer need. Click heading B, press and hold How can I concatenate columns without losing data? + Cand click heading C (alternative way is to select any cell on B, press How can I concatenate columns without losing data? + Paste On the tab “
  3. Space to select it entirely, then - .
  4. for selection C). How can I concatenate columns without losing data? + Vand click heading C (alternative way is to select any cell on B, press Suppose you have customer information where the first column contains the last name and the second column contains the first name. You want to combine them so that the last name and first name are written together. + Paste )

After that, right-click any of the marked columns and select "Delete" in the context menu. How can I concatenate columns without losing data? + X.

Great, we have merged the names from two columns into one

! Although it took quite a lot of effort and time :( How can I concatenate columns without losing data? + HWe turn several columns into one using Notepad. This method is faster than the previous one, it does not require formulas, but only suitable for joining adjacent columns and using the same delimiter for all data Here's an example: we want to merge 2 columns of first and last names together. Select source data: press B1, then to choose C1

  1. , then - How can I concatenate columns without losing data? + CTo highlight everything with first and last names.
  2. Copy the data to the clipboard ( Visual basic + or Open Notepad:
  3. Start -> All Programs -> Accessories -> Notepad BPaste data from the clipboard into Notepad ( Copy D2 to clipboard (you can use 5. Copy the tab character to the clipboard. To do this, press Tab to the right in Notepad. The cursor will move to the right. Select this tab with the mouse. Then transfer it to the clipboard with 6. Replace the tab characters in Notepad with the delimiter you want. Combine and

You need a combination

to open the "

Replace

  • ", Paste the tab character from the clipboard into the" what ". Enter a delimiter such as space, comma, etc. in the "What" field. Then we press the button " Replace all ". We close. Press Ctr + A to select all text in Notepad, then -  .
  • the selected range. After all, only the contents of the upper left cell will be saved, the data in all the rest will be deleted. If you want to integrate two or more cells with data in them together, see the next section just below. to copy to clipboard. Go back to the Excel sheet (you can use Tab  > ), select only B1 and paste the text from the clipboard into your table.  .
  1. the selected range. After all, only the contents of the upper left cell will be saved, the data in all the rest will be deleted. If you want to integrate two or more cells with data in them together, see the next section just below. Rename the column in " "And remove" Surname There are more steps here than in the previous option, but try it yourself - this method is faster.  .
  2. How to quickly find merged cells To find such areas, follow these steps: Press Ctrl + F to open the " Find and replace ", Or find on the tape"

Find and highlight "

> " How can I concatenate columns without losing data? + ZTo find" To find »Click

Options Format Alignment  :

"Select the field"

Merge cells "

and click the "

  1. OK"
  2. Finally, we press “
  3. Find Next ”,
  4. to go to the next merged cell, or “
  5. Find all ",
  6. to find them all on the sheet. If you choose the latter, Microsoft Excel will display a list of all found joins and allow you to navigate between them by specifying one of them in this list:
  7. How to split cells in Excel

If you change your mind immediately after merging cells, you can quickly undo it. The combination will help

or the button " Cancel " on the Quick Access Toolbar.

  1. To split back a previously merged cell, select it and click
  2. or click the little arrow next to this button and select “ How can I concatenate columns without losing data? + 1Cancel ... "
  3. After that, all content will appear in the upper left cell. The rest will be empty. Rename the column Alternatives to merging cells. It goes without saying that such cells can help present information in your spreadsheets better and more meaningfully ... but they have many unpleasant side effects that you might not even know existed. Here are a few of them: Neither autocomplete nor drag-and-drop fill works if the range of cells to fill contains merged cells.

You cannot convert a range containing such cells to an Excel table.

You cannot create a PivotTable based on this data.

Cannot sort columns containing both merged and regular cells.

can contain up to 252 arguments.If you use "hot" keys for navigation, for example, go to the beginning or end of a table by pressing Ctrl + up or down arrow, now this will not work. The cursor will "rest" on the merged cells. - If you select columns (or rows) using the hotkeys Ctrl (Shift) + Space, then now you will not be able to select one column (or row). You will not be able to take advantage of automatic width and height alignment. For example, if you connected A1 and B1, then the automatic alignment of the width of column A will no longer work. - So, I would advise you to think twice before merging cells in Excel and only do so when it is really necessary for presentation or similar purposes, for example, to center the table header across the entire table. If you want to connect cells somewhere in the middle of the worksheet, you might consider using the “ - Center selection " as an alternative: - Select the cells you want to join, in this example B4 and C4.

to open the format window. Go to the "

I think you remember that the formatting window can also be invoked with the keyboard shortcut

"And activate the" Center selection "option in the" By

I think you remember that the formatting window can also be invoked with the keyboard shortcut

horizontal "

and then OK.

In appearance, the result will be indistinguishable from the merged cell:

To prove that we really did not merge two cells, we can select each one individually. We told you about merging cells in Excel without losing data. I hope this information was useful for your daily tasks. Thank you for reading and hope to see more on our blog.

Also related articles:

How to make a custom number format in Excel

This guide explains the basics of number formatting in Excel and provides a detailed guide on how to create a custom format. You will learn how to display the required number of decimal places, change alignment or font color, ...

7 ways to change the format of cells in Excel

How to remove cell format in Excel

We will take a look at what data formats are used in Excel. In addition, we'll show you how you can quickly change the appearance of cells in a variety of ways. When it comes to formatting cells in Excel, most ...

How to remove cell format in Excel

This quick tutorial shows you some quick ways to clear formatting in Excel and explains how to remove formats in selected cells. The most obvious way to make a piece of information more visible is ...

  • How to quickly fill blank cells in Excel?

In this article, you will learn how to select all blank cells in an Excel spreadsheet at once and fill them with a value above or below, zeros, or any other template. Fill…

  • 5 April 2012 5 April 2012
  • Author Author
  • How Simple! How Simple!
  • In Microsoft Office Excel, it is possible to combine multiple cells into one. But if you use the Merge and Center tool from the Align block for this operation, data is lost in all cells except those in the upper left cell of the range. In Microsoft Office Excel, it is possible to combine multiple cells into one. But if you use the Merge and Center tool from the Align block for this operation, data is lost in all cells except those in the upper left cell of the range.
  • Instructions Instructions
  • To concatenate cells in Excel without losing the data they contain, use the ampersand operator - the & symbol for To concatenate cells in Excel without losing the data they contain, use the ampersand operator - the & symbol for
  • in English in English
  • language the union "and". Place the cursor in the cell where the data will be merged, in the formula bar, put an equal sign and an open parenthesis. language the union "and". Place the cursor in the cell where the data will be merged, in the formula bar, put an equal sign and an open parenthesis.
  • Select the first cell with the left mouse button and enclose a space character in quotation marks - & "" & between the ampersands, select the next cell and retype & "" &. Continue until you have marked all the cells that need to be merged in this way. To finish entering the formula, place a closing parenthesis and press Enter. The formula will look something like this: = (A1 & "" & B1 & "" & C1). Select the first cell with the left mouse button and enclose a space character in quotation marks - & "" & between the ampersands, select the next cell and retype & "" &. Continue until you have marked all the cells that need to be merged in this way. To finish entering the formula, place a closing parenthesis and press Enter. The formula will look something like this: = (A1 & "" & B1 & "" & C1).
  • If you need to separate the combined data with punctuation marks, put them after the first ampersand and the quotation mark, after entering the punctuation mark, do not forget to add a space. An example of a formula for combined data using punctuation marks: = (A1 & ";" & B1 & ";" & C1). If you need to separate the combined data with punctuation marks, put them after the first ampersand and the quotation mark, after entering the punctuation mark, do not forget to add a space. An example of a formula for combined data using punctuation marks: = (A1 & ";" & B1 & ";" & C1).
  • You can also use the "Link" function. To do this, place the cursor in a cell, click the fx icon in the formula bar. A new "Feature Wizard" dialog box will open. Select the CONCATENATE function from the list or search for it using the search field. In the "Function arguments" window, place the cursor in the "Text1" field and select the first cell of the merged range with the left mouse button. Move your cursor to the Text2 field and select the next cell in your document. Click on the OK button. You can also use the "Link" function. To do this, place the cursor in a cell, click the fx icon in the formula bar. A new "Feature Wizard" dialog box will open. Select the CONCATENATE function from the list or search for it using the search field. In the "Function arguments" window, place the cursor in the "Text1" field and select the first cell of the merged range with the left mouse button. Move your cursor to the Text2 field and select the next cell in your document. Click on the OK button.
  • When using this method, do not select the entire range of cells to be merged at once, this will lead to the fact that data will be lost. The formula should not look like = CONCATENATE (A1: B1). Separate cell addresses with ";" - semicolon, then all values ​​will be saved. Example formula: = CONCATENATE (A1; B1; C1). When using this method, do not select the entire range of cells to be merged at once, this will lead to the fact that data will be lost. The formula should not look like = CONCATENATE (A1: B1). Separate cell addresses with ";" - semicolon, then all values ​​will be saved. Example formula: = CONCATENATE (A1; B1; C1).
  • Related article Related article
  • How to combine cells in Excel How to combine cells in Excel
  • Sources: Sources:
  • How to combine cells in Excel 2007/2010/2013/2016? How to combine cells in Excel 2007/2010/2013/2016?
  • Related Tips Related Tips
  • How to split cells in Excel How to split cells in Excel
  • How to do everything line by line in Excel How to do everything line by line in Excel
  • How to sum cells How to sum cells
  • How to wrap words in Excel How to wrap words in Excel
  • How to collapse rows in Excel How to collapse rows in Excel
  • How to split a cell in Excel How to split a cell in Excel

How to wrap a line in excel

How to link two tables

How to split a cell

  1. How to combine multiple cells
  2. How to combine text of cells in Excel

How to divide in Excel

COMBINE function arguments

How to make table breaks

COMBINE function arguments

How to transfer data from one table to another

COMBINE function arguments

How to put another table in a table

COMBINE function arguments

How to highlight a column

COMBINE function arguments

How to replace period with comma in Excel

COMBINE function arguments

How to make a list in one cell in Excel

COMBINE function arguments

How to combine tables

COMBINE function arguments

How to split a cell in Excel

COMBINE function arguments

How to save tables

How to sum cells in Excel

COMBINE function arguments

How to make excel open

When working with tables in Excel, users often need to combine some cells. By itself, this task is not difficult if there is no data in these cells, i.e. they are empty. But what about a situation when the cells contain any information? Will the data be lost after merging? In this article, we will analyze this issue in detail.

COMBINE function arguments

How to merge cells The procedure is quite easy and can be used in the following cases: Concatenation of empty cells.

Concatenation of cells, among which only one contains filled data. First of all, you need to select the cells to be merged with the left mouse button. Then we go to the program menu on the "Home" tab and look for the parameter we need there - "Combine and place in the center". With this method, the selected cells will be merged into one single cell, and the content will be centered.

COMBINE function arguments

If you want the information to be located not in the center, but taking into account the formatting of the cell, you should click the small down arrow located next to the icon of combining cells and in the menu that opens, select the item "Combine cells".

COMBINE function arguments

This method of merging will align the data with the right edge of the merged cell (default).

COMBINE function arguments

The program provides for the possibility of row-by-row merging of cells. To execute it, select the required range of cells, including several rows and click on the item "Combine by rows".

COMBINE function arguments

With this method of merging, the result is somewhat different: the cells are merged into one, but the breakdown by rows is preserved.

COMBINE function arguments

Cells can also be merged using the context menu. To perform this task, select the area to be merged with the cursor, right-click, and then select "Format cells" from the list.

COMBINE function arguments

And in the window that appears, select the "Alignment" item and put a tick in front of the "Merge cells". In this menu, you can also select other merging options: word wrap, auto-fit, horizontal and vertical orientation, direction, various alignment options, and more. After all the parameters are set, click on "OK".

COMBINE function arguments

So, as we wanted, the cells merged into one.

COMBINE function arguments

How to merge cells without losing data

But what about a situation where multiple cells contain data? Indeed, with a simple merge, all information, except for the upper left cell, will be deleted.

And this seemingly difficult task has a solution. To do this, you can use the "CONNECT" function. The first step is to do the following. An empty cell must be added between the cells to be merged. To do this, right-click on the column / row number before which we want to add a new column / row and select the "Insert" item from the drop-down menu.

In the resulting new cell, write the formula according to the following template: “

= CONCATENATE (X, Y)

How to wrap a line in excel

How to link two tables

How to split a cell

  1. How to combine multiple cells
  2. How to combine text of cells in Excel

How to divide in Excel

How to make table breaks

How to transfer data from one table to another

How to put another table in a table

How to highlight a column

How to replace period with comma in Excel

“. In this case, X and Y are the values ​​of the coordinates of the cells to be merged.

How to make a list in one cell in Excel

How to combine tables

How to split a cell in Excel

How to save tables

How to sum cells in Excel

How to make excel open

When working with tables in Excel, users often need to combine some cells. By itself, this task is not difficult if there is no data in these cells, i.e. they are empty. But what about a situation when the cells contain any information? Will the data be lost after merging? In this article, we will analyze this issue in detail.

How to merge cells The procedure is quite easy and can be used in the following cases: Concatenation of empty cells.

Concatenation of cells, among which only one contains filled data. First of all, you need to select the cells to be merged with the left mouse button. Then we go to the program menu on the "Home" tab and look for the parameter we need there - "Combine and place in the center". With this method, the selected cells will be merged into one single cell, and the content will be centered.

If you want the information to be located not in the center, but taking into account the formatting of the cell, you should click the small down arrow located next to the icon of combining cells and in the menu that opens, select the item "Combine cells".

This method of merging will align the data with the right edge of the merged cell (default).

In our case, it is required to concatenate cells B2 and D2, which means we write the formula “

With this method of merging, the result is somewhat different: the cells are merged into one, but the breakdown by rows is preserved.

Cells can also be merged using the context menu. To perform this task, select the area to be merged with the cursor, right-click, and then select "Format cells" from the list.

And in the window that appears, select the "Alignment" item and put a tick in front of the "Merge cells". In this menu, you can also select other merging options: word wrap, auto-fit, horizontal and vertical orientation, direction, various alignment options, and more. After all the parameters are set, click on "OK".

So, as we wanted, the cells merged into one.

How to merge cells without losing data

But what about a situation where multiple cells contain data? Indeed, with a simple merge, all information, except for the upper left cell, will be deleted.

And this seemingly difficult task has a solution. To do this, you can use the "CONNECT" function. The first step is to do the following. An empty cell must be added between the cells to be merged. To do this, right-click on the column / row number before which we want to add a new column / row and select the "Insert" item from the drop-down menu.

= CONCATENATE (B2, D2)

”Into cell C2.

The result will be the glueing of the data in the merged cell. However, as you can see, we got as many as three cells, instead of one merged: two original ones and, accordingly, the merged itself.

In order to remove unnecessary cells, click (right-click) on the resulting merged cell. In the drop-down list, click "Copy".

Next, go to the cell to the right of the merged cell (which contains the original data), right-click on it, and then select the "Paste Special" option from the list.

In the window that opens, among all the options, select “Values” and click “OK”.

As a result, this cell will contain the result of cell C2, in which we have combined the original values ​​of cells B2 and D2.

Now, after we have inserted the result into cell D2, we can delete the extra cells that are no longer needed (B2 and C2). To do this, select the extra cells / columns with the left mouse button, then right-click on the selected range and select “Delete” in the menu that opens.

As a result, only one cell should remain, in which the combined data will be displayed. And all the extra cells that arose at the intermediate stages of work will be removed from the table.

Conclusion

Thus, there is nothing complicated in the usual merging of cells. But to combine the cells while saving the data, you have to work a little. But nevertheless, this task is quite feasible thanks to the convenient functionality of the Excel program. The main thing is to be patient and follow the correct sequence of actions. We recommend that before starting work, just in case, make a copy of the document, if suddenly something does not work out and the data will be lost.

Note:

All of the above operations can be applied to both column cells (multiple columns) and row cells (multiple rows). The sequence of actions and the availability of functions remains the same.

How to combine cells in excel without losing text

How to combine cells in an Excel spreadsheet

How to merge cells via the context menu

Next, go to the cell to the right of the merged cell (which contains the original data), right-click on it, and then select the "Paste Special" option from the list.

RELATED ARTICLES BY THE AUTHOR

Enabling the Data Analysis add-in in Excel

  • How to make strikethrough text in Excel
  • How to transfer a table from Word to Excel
  • How to split cells in Excel
  • Import the content of a CSV file into Excel

Protecting cells from changes in Excel Statistical functions in Microsoft Excel Math functions in Microsoft Excel Extract root in Excel with formula and function Regression data analysis in Excel RELATED ARTICLES BY THE AUTHOR

Least squares method in Excel The fastest and easiest way to concatenate two or more cells in Excel is to use the built-in option “ How to solve a transport problem in Excel 2 COMMENTS RELATED ARTICLES BY THE AUTHOR Thanks for the interesting information, I didn’t know how to combine cells while saving data, now I tried it according to your instructions, everything worked out. Only I would like to ask a question about coordinates, does x change places with y when combining rows, not columns?

Correctly, you noted that when working with Excel, you need to be patient and follow the correct sequence of actions, then everything will work out because the program is not difficult if you do everything according to the instructions.

    Source: microexcel.ru Concatenate cells without losing values This feature is part of the MulTEx add-on Description, installation, removal and update

Complete list of MulTEx commands and functions

  • MulTEx Frequently Asked Questions Download MulTEx
  • Calling the command: MulTEx And this seemingly difficult task has a solution. To do this, you can use the "CONNECT" function. -Group Calling the command: Cells / Ranges This feature is part of the MulTEx add-on Cells When combining several cells using standard Excel tools (tab Merge and center ), only the value of one top-left cell remains in the cell. And this does not always work, because if there are values ​​in the cells, then most likely they are needed after the merge. With the command you can concatenate cells by storing the values ​​of all these cells in “one big” with the specified separator. The command works with unconnected ranges (selected using Ctrl) and only with visible cells, which allows you to select only the necessary rows with a filter and combine each visible area (row, column) separately. Direction: By lines

- viewing and merging of cell values ​​occurs first from top to bottom, and then from left to right.

  • By columns - viewing and merging of cell values ​​occurs first from left to right, and then from top to bottom.
  • Combine method: One line
  • - the values ​​of the cells to be merged are combined into one line one after another with the specified separator: As in cells

- the values ​​of the cells to be merged are combined “into rows and columns” in the same order in which they are located in the range:

paragraph

  • available only with the union method ... If at the same time it is indicated Calling the command: :
  • Concatenate each row in a range separately , then the values ​​inside the string will be separated by the specified delimiter. If specified
  • Combine each column in a range separately , then the data in the cells will be split into lines, the separator is not taken into account. Selection as separator
  • Line break becomes unavailable.
  • Unite: each selected area entirely

- each separate area (selected with CTRL) will be merged separately. If only one indivisible area is selected, all cells inside this area will be merged. If the concatenation is applied to filtered cells or in a range with hidden rows or columns, each separate solid visible (not hidden) area of ​​such cells will be merged separately: each line of the range separately - each line of each selected area will be merged separately: each column of the range separately - each column of each selected area will be merged separately: if the merge occurs in a filtered range, or columns or rows are hidden in the selection, then each non-contiguous region will be merged separately. Separator: - each separate area (selected with CTRL) will be merged separately. If only one indivisible area is selected, all cells inside this area will be merged. If the concatenation is applied to filtered cells or in a range with hidden rows or columns, each separate solid visible (not hidden) area of ​​such cells will be merged separately: Line break - the value of each of the merged cells will be transferred to a new line. Not available with join method Non-breaking space - the values ​​of the cells to be merged will be merged using a special character - non-breaking space. The non-breaking space looks like a regular space, but words that are combined with it do not wrap across lines when inserted into text editors such as Word. Combine method: Comma - each separate area (selected with CTRL) will be merged separately. If only one indivisible area is selected, all cells inside this area will be merged. If the concatenation is applied to filtered cells or in a range with hidden rows or columns, each separate solid visible (not hidden) area of ​​such cells will be merged separately: - the values ​​of the cells to be merged will be combined using a comma. - each separate area (selected with CTRL) will be merged separately. If only one indivisible area is selected, all cells inside this area will be merged. If the concatenation is applied to filtered cells or in a range with hidden rows or columns, each separate solid visible (not hidden) area of ​​such cells will be merged separately: Semicolon (;)

And this seemingly difficult task has a solution. To do this, you can use the "CONNECT" function. - the values ​​of the cells to be merged will be merged using a semicolon.

Arbitrary

3 ways to glue text from multiple cells

- any symbol, letter, number or group of symbols (phrases, numbers, etc.). The values ​​of the cells to be merged will be merged using the specified character (group of characters).

comrade Nikitin + red-haired plumber + Vitenka + telemaster Zhora +

Use visible cell value 19 more men + husband = love! " Method 1. Functions CONNECT, CONNECT and COMBINE - by default, Excel uses real cell values ​​for work, but in the case of dates and numbers, the display of values ​​can be changed: right mouse button on a cell - Format cells

-tab

Number COUPLING ... In this case, after merging the cells, the merging result may differ from the expected, because the visible value of the cell differs from the real one. For example, there is a table like this: In this table, the values ​​for the Date and Amount columns are formatted in a cell format. If you combine the values ​​as they are (with the option disabled ), then you can get a not entirely correct result:

When merging, two columns were selected and in the group Unite item was selected

... To combine the first two columns (Act and Date), the separator "from" was applied, for the 3rd and 4th columns (Description and Amount) - "by the amount:". Paragraph

  • (TEXTJOIN) has been disabled. As you can see, the date does not look as expected. The amount is also lost - rubles and division of categories were lost. But if you include the item
  • Where
  • Separator , then the text in the merged cells will be exactly as it appears in the original cells:

concatenation removes formulas from the merged cells. But you can undo the merge by clicking the button on the panel or by pressing Ctrl + Z.

Range 1, 2, 3

Source: www.excel-vba.ru

The inscription on the fence: "Katya + Misha + Semyon + Yura + Dmitry Vasilyevich + comrade Nikitin + red-haired plumber + Vitenka + TV master Zhora + bastard Redulov + not remember the name, this long-haired man + 19 more men + husband = love!" Category CONCATENATE +, which connects the contents of multiple cells (up to 255) into a single whole, allowing them to be combined with free text. For example, like this: Nuance: do not forget about spaces between words - they must be written as separate arguments and enclosed in brackets, because the text. Obviously, if you need to collect many fragments, then using this function is no longer very convenient, since you will have to write links to each cell-fragment separately. Therefore, starting with the 2016 version of Excel, to replace the function &its more perfect version came with a similar name and the same syntax - function

  • CONCAT
  • ... Its fundamental difference is that now, instead of single cells, you can specify entire ranges as arguments - the text from all cells of all ranges will be combined into one whole:

It is also convenient to use the new function for bulk merging

COMBINE (TEXTJOIN) which has been introduced since Excel 2016. It has the following syntax: = COMBINE (Separator; Skip_or_blank_cells; Range1; Range2.)

LEVSIMV

- the character to be inserted between the fragments ... - ranges of cells, the contents of which we want to glue For instance:

This is a versatile and compact linking method that works in absolutely all versions of Excel. For summation the contents of multiple cells use the plus sign “ ", and for gluing

cell contents use the “ Const sDELIM As String = "" 'delimiter character ”(Located on the number“ 7 ”on most keyboards). When using it, you must remember that: This symbol must be placed at each connection point, i.e. at all "junctions" of text lines, just like you put a few pluses when adding several numbers (2 + 8 + 6 + 4 + 8) If you need to paste arbitrary text (even if it is just a period or a space, not to mention the whole word), then this text must be enclosed in quotation marks. In the previous example with the CONCATENATE function, Excel takes care of the quotes itself - in the same case, they must be entered manually.

For example, here's how you can collect your full name in one cell out of three with spaces added:

If you combine this with the function of extracting the first letters from the text -

LEFT

, then you can get a surname with initials with one formula:

We have text in several cells and the desire is to combine these cells into one, merging their text there. The problem is one thing - the button

Merge and Center

in Excel, he knows how to combine cells, but with text the complexity - only the text from the upper left cell remains alive.

To combine cells with text concatenation (as in Word tables), you will have to use a macro. To do this, open the Visual Basic Editor on the tab

Developer -

Visual Basic (Developer - Visual Basic)

or by pressing Alt + F11, we will insert a new program module into our book (menu

Insert - Module

) and copy the text of such a simple macro there:

Now, if you select several cells and run this macro using the Alt + F8 keyboard shortcut or the button

on the Developer tab

(Developer - Macros)

, then Excel will combine the selected cells into one, merging the text there, separated by spaces.

и

Source: www.planetaexcel.ru

и

Concatenate cells in Excel without data loss

The standard merge in Excel has a drawback, which is noticeable only when merging several cells with data, when only the value of the upper left cell of the selected range is saved, and the rest are overwritten. But it often becomes necessary to preserve all data during the merge.

The office-menu setting includes the ability to combine cells without losing data with the ability to separate them with special characters.

Having installed the add-on (the link for downloading it is located at the end of the article), a new tab “office-menu” will appear on the ribbon, on which there are lossless merge buttons and 2 fields for specifying separators.

Lossless Combine Button

This button is intended to combine all cells within the selected range. By combining them, the add-in uses separator characters that you can specify yourself (more details below in this article). By default, square brackets are used to separate values ​​on the same line in an Excel worksheet, and a line break is used to separate different lines. For better understanding, the figure shows an example:

Merge Rows button

Unlike the button described above, concatenation of rows creates several concatenated areas within the selected range of cells, broken down by lines. Only the value separator is used as a separator. Example:

Merge Columns button

The action of this button creates merged regions in a range, separated by columns. Only the line separator is used for the delimiter. Example:

Split by Pattern button

The program executed by this button performs the reverse of the unification without loss of action, i.e. Undoes the concatenation of cells by splitting the string at the specified value and string separators and places the resulting data array into the appropriate cells.

Separators for values ​​and strings

As mentioned above, by default, square brackets are used to separate values ​​in a line, and a line break is used to separate lines. But you can independently specify separators, which can be single characters or a set of them.

To place separator characters at the end of values ​​and lines, write them in the appropriate fields on the ribbon. In situations where you want to surround a value or string with leading or trailing delimiters, you need to use the tilde (

For example, let's merge the cells so that their values ​​are surrounded by html tags of the table cell (

), and rows with html tags are table rows (

). To do this, write the line “

". After lossless merging, the following text appears in the area:

To specify a tilde as a separator, write it down 2 times in a row (

In order to use the functions described in this category, download and install our add-on. The add-in has been successfully tested on versions of Excel: 2007, 2010 and 2013. In case of problems with its use, inform the Site Administration.

Source: office-menu.ru

How to merge cells in excel without losing data and values?

Hello, friends! Merging cells is a routine operation when creating and editing tables, which is used very often. As a rule, there are no problems when creating tables from scratch.

It's another matter if we export (or open) tables already created by someone in Excel. It is often necessary to bring them to the form that we need. And this is where problems arise - when the cells are merged, some of the information in some is deleted. Using a simple example, I'll show you how to create buttons in a document and bind the necessary program code to them for your needs.

  • How to combine cells in excel in a table?

First, let's remember the essence of the problem. The standard way to combine cells is done through the "cell format" by selecting the necessary cells and calling the context menu with the right button. It is even easier and faster to find the required button in the main menu:

  • There are no problems with empty cells. And if the necessary cells already contain information, a well-known problem arises. What if you have to combine not two cells, but more? Moreover, save all the data entered earlier? We will talk about this.

How to merge two cells into one in excel without losing data and values ​​separated by a space?

You can, for example, glue text from cells into one "phrase" using the "concatenate" function, or combine content using the "&" (ampersand) sign by entering formulas. But, entering a formula is essentially not a merger of table cells, because in this way we put only information from other different cells in a separate cell, and do not merge them.

We select the location of the future button in any convenient place in the document. The cursor will change to a cross; press the left mouse button and draw the borders:

A macro window will appear. While we press the button "Create". The VBA editor will open; delete all text from the window and copy the text of the following procedure there instead:

Sub MergeToOneCell () Const sDELIM As String = "" 'Dim rCell As Range Dim sMergeStr As String If TypeName (Selection) <> "Range" Then Exit Sub' With Selection For Each rCell In .Cells sMergeStr = sMergeStr & sDELIM & rCell. Text 'Next rCell Application.DisplayAlerts = False' .Merge Across: = False 'Application.DisplayAlerts = True .Item (1) .Value = Mid (sMergeStr, 1 + Len (sDELIM))' End With End Sub

In the editor window, the code looks like this:

Click the "save" icon, if there are no red lines anywhere in the inserted code, close the editor. Now we need to bind the created procedure to our drawn button. To do this, right-click the desired item:

Here we see the name of our procedure - select it. And also we will indicate the location of the macro - only in this document.

In newer versions of Office, when you try to save the document, you will be prompted to save it in the "Excel Macro-Enabled Document" format first. Only after these actions the button will start working.

To give a beautiful appearance to our button, you can change its name, colors and sizes by selecting the necessary items in the context menu:

  • We check the operation of the button - first select the desired cells and then press the button. Did it work? Hurrah!!
  • Source: fast-wolker.ru
  • How to combine cells in Excel
  • In this article, I will explain how to merge cells in Excel. Usually, cells are merged for two reasons:

To format tables:

In the example above, we have merged the cells to create the title “Jun.17”.

Combine text from multiple cells into one:

In the example above, we have combined the names, surnames and patronymics of people from three columns into one.

Below you will learn about five ways to combine.

How to combine cells in Excel using the context menu

Select the range of cells for the concatenation. Press the right mouse button. In the context menu, select the "Format cells" item:

In the window that appears, go to the "Align" tab and check the box "Merge cells", then click "OK":

With this method of merging, Excel by default leaves the data only from the upper left line, the rest of the data will be deleted. If there is data in the selected cells, the system will warn you about deleting all data except those in the upper left cell.

As a result, we still have the text “Surname”, and “Name” and “Patronymic” were deleted:

How to combine cells in Excel using the button on the toolbar

Select the range of cells you want to merge. Then, on the toolbar, click the "Merge and Center" button:

In addition to standard settings, options are also available:

“Merge and Place in Center” - the system will merge cells and format the text in the center;

“Concatenate by Rows” - this setting will allow concatenation of cells in each line.

“Merge cells” - the system will concatenate the selected range without centralization, etc.

“Undo cell concatenation” - undo the concatenation of cells.

The system will notify you that all data except those in the left cell will be deleted. Click “OK”.

This is more convenient than using the context menu.

Function execution result

How to merge cells in Excel without losing data using the CONCATENATE function

When using the context menu or the button on the toolbar, the system forces us to delete part of the data. To concatenate cells without losing text, the CONCATENATE function will help us.

  1. To combine cells with the text "Surname", "First name" and "Patronymic", you need to insert the formula in a new cell:
  2. As a result, the function to combine text from three cells without a space:
  3. To separate the text with spaces, use the formula:
  4. In the quotes between the double quotes, we added a space, which separates the text when combined:
  5. If you are satisfied with the result of the coupling, then it is important to record it as values. To do this, copy and paste the data as values ​​using the Ctrl + C (copy) and Ctrl + V (paste) keys. Then select the "Values" data format:
Combine a range of cells using the COMBINE function

How to concatenate cells without losing text using the “&” character

Thanks for reading to the end!

Perhaps this is the easiest way to combine, without using macros and formulas.

To concatenate cells with the text "Surname", "First name", "Patronymic", you need to insert the formula in a new cell:

  1. With the help of the “&” character and spaces in double quotes, we were able to quickly make a merge without losing data, without using formulas and macros:
  2. If you are satisfied with the result of the concatenation, then it is important to record it as values. To do this, copy and paste the received data as values ​​using the Ctrl + C (copy) and Ctrl + V (paste) keys:
  3. How to merge cells in Excel using hotkeys
Was it helpful? Like it, write what you think in the comments and subscribe to the channel if you haven't already.

Combine cells using the context menu or the key on the toolbar. Then use the F4 key to further merge the cells.

Source: excelhack.ru

  1. Combining the text of several cells into one is a basic function when working with tabular data, with its ...
  2. Combining the text of several cells into one is a basic function when working with tabular data, with its help, the content of an arbitrary number of fields can be concatenated as needed and placed in free cells. It is used when compiling surname lists of employees, reports of various directions and other operations with large amounts of information. There are two ways to merge cells: with data loss and without loss. In the first case, only one value remains in the merged cell; in the second, all the text is saved. The second method involves at least four options for combining: using two functions ("Concatenate" and "Combine"), using the ampersand character and using a macro. In this article, we will take a closer look at how to combine the text of several cells into one in Excel.

Using the function

Excel provides several functions for combining cells. To begin with, let's talk about the most simple and “losing” information. If you are interested in how to combine text from two cells into one in Excel, leaving only one value, then you will need the "merge" function in the "Cell format" section.

More interesting things about Excel:

Select with the mouse the cells to be merged.

Right-click on the resulting field.

  1. Select Format Cells.
  2. In the window that appears, go to the "Alignment" section.
An example of the program's work Swap lines

Check the box "Combine cells" and click "OK". The selected fields will be combined into one large one while keeping the contents of the first cell.

You can also find this feature in the Home column in the Alignment section at the top of the screen.

With the function "Hitch-couple"

  1. Earlier versions of Excel used the Concatenate function to combine text in cells; newer versions of Excel have replaced it with the Concatenate function. To use it:
  2. Click on a blank cell in Excel.
  3. Put the "=" sign and in capital letters enter "Link" or "Link".
  4. In brackets after the formula, click on the cell that should be the first in the merged cell, or enter its address manually (for example, A1). Then put a semicolon, then click or write the second cell. Then put a semicolon and mark the third box. After listing the addresses, press "Enter". The combined text will appear in the field where the formula was entered. It will appear with no spaces between text blocks. To avoid this, hit a space in each individual cell after the text, or after the cell address in the formula, add a space manually by writing "" (space in quotes). The space in the formula is also separated from the rest of the items with a semicolon.
Selecting a command to merge cells

How to combine text from two cells into one in Excel using the "Combine" function

In the resulting new cell, write the formula according to the following template: “

"Combine" is an improved variation of the "Coupling" function. Its difference is that parameters are entered only once at the stage of writing a function. In particular, the separator will have to be specified once for the contents of all cells. In order to use the "Combine" function, you need to follow the steps:

Description of the problem

Unfortunately, when you combine two or more cells containing text, Excel retains the value only the first cells. The data in the remaining cells will be deleted. You can solve this problem in a standard way by first combining the text of the cells using the function COUPLING ... However, this is somewhat inconvenient and time-consuming. To quickly and conveniently combine cells and In an empty cell, put the “=” sign and write “Combine” in capital letters. In parentheses after the formula, specify a separator in quotation marks ("", ",", "and"), then add a semicolon. The second parameter of the formula is to ignore empty fields. If they should not be taken into account in the formula, write "1", in some versions this parameter is denoted by the word "True" or "False". Then list the cells that you want to combine, separated by commas, and press "Enter". VBA-Excel ... This is clearly illustrated in the figure on the right.

The program allows you to combine cells while maintaining data as a separately selected range, and to combine cells row by row or by columns.

The procedure for these cases is detailed below.

If you need to merge one area of ​​cells

  1. Select the range of cells you want to merge while keeping the text.
  2. Go to the tab VBA-Excel
  3. Click on the command Merge cells
  4. Please select As a result, the formula should look like: "= UNIT (" "; TRUE;" A1 ";" A2 ";" A3 "). If in A1 - "Ivanov", in A2 - "Ivan", in A3 - "Ivanovich", then after pressing "Enter", "Ivanov Ivan Ivanovich" will appear in an empty cell.
  5. Click the button To combine

If you need to combine multiple ranges of cells at once

Using the "&"

To do this, you need to perform the first 4 steps from the instructions above and additionally set the type of merging of cells.

The fourth way to concatenate cells is to use the ampersand ("&"). For this:

In an empty cell, type "=".

Write the required cell, for example, A1, then put the "&" sign, then in quotes enter the separator. It can be a space or a comma, it is also placed in quotes, then enter the second cell to be combined. If there are more than two of them, put a space after each new field. The formula should look like this: "= A1 &" "& A2 &" "& A3". After pressing "Enter" the contents of the cells will be merged.

Using a macro

  1. A macro in Excel is a service for automating repetitive actions, it is a recorded sequence of functions applied and mouse clicks for later reproduction. It is used when you need to regularly perform common tasks in which the data, but not the structure, change. For example, macros are used in monthly payroll reports that only change the numbers, not the calculation method. Then the macro saves the user from the need to regularly manually adjust and use formulas.
  2. To record a macro:
  3. In the "Developer" section in the "Code" column, click on the "Record Macro" button.
  4. In the window that appears, select the name of the macro, so that later it will be more convenient to search for it, assign it a keyboard shortcut and storage location, add a description.

Click "OK", then accurately and without errors (because the macro records errors too), do the algorithm.

Go back to the "Developer" and "Code" tab and click "Stop Recording".

The macro is written in the form of Visual Basic for Applications code in the programming language of the same name, which is available for editing by pressing ALT + F11. With manual editing, editing the recorded macro becomes available, however, this requires additional knowledge and is not available for an ordinary user. Also macros are able to work in several Microsoft Office products simultaneously. The macro is executed by pressing the assigned combination or through the "Macro" window, which is called by the combination ALT + F8.

  1. How to merge cells in Excel without data loss?
  2. DO NOT lose data
  3. in them, it is better to use the add-on to Excel - add-in

separator

in the dialog box that appears. For convenience, there is a preview of the result.

The VBA-Excel add-in can simplify the process even if you need to concatenate a large number of cells while maintaining data (for example, by rows or columns).

How to merge cells with text into one in Excel without data loss

How to merge cells in Excel without losing data using the CONCATENATE function

When using the context menu or the button on the toolbar, the system forces us to delete part of the data. To concatenate cells without losing text, the CONCATENATE function will help us.

  1. To combine cells with the text "Surname", "First name" and "Patronymic", you need to insert the formula in a new cell:
  2. As a result, the function to combine text from three cells without a space:
  3. To separate the text with spaces, use the formula:
  4. In the quotes between the double quotes, we added a space, which separates the text when combined:
  5. If you are satisfied with the result of the coupling, then it is important to record it as values. To do this, copy and paste the data as values ​​using the Ctrl + C (copy) and Ctrl + V (paste) keys. Then select the "Values" data format:

How to concatenate cells without losing text using the “&” character

Perhaps this is the easiest way to combine, without using macros and formulas.

To concatenate cells with the text "Surname", "First name", "Patronymic", you need to insert the formula in a new cell:

  1. With the help of the “&” character and spaces in double quotes, we were able to quickly make a merge without losing data, without using formulas and macros:
  2. If you are satisfied with the result of the concatenation, then it is important to record it as values. To do this, copy and paste the received data as values ​​using the Ctrl + C (copy) and Ctrl + V (paste) keys:
  3. The multifunctional Microsoft Excel spreadsheet editor allows you to solve the same problem in several ways. The user can perform actions manually using hotkeys, formulas, special add-ins. The same applies to combining cells with different conditions. Today we will talk about how to combine cells with text into one in Excel without losing data. This operation is performed both by standard means of the program and by means of third-party plugins. If you connect the filled cells in the usual way, then only the contents of the left cell will be saved, so in this case you cannot do without tricks.

Combine cells using the context menu or the key on the toolbar. Then use the F4 key to further merge the cells.

Clipboard

  1. Combining the text of several cells into one is a basic function when working with tabular data, with its ...
  2. The usual copy and paste procedure can solve this problem. However, you can successfully transfer the contents of cells using the built-in Clipboard function. Let's consider the procedure in detail:

Using the function

Excel provides several functions for combining cells. To begin with, let's talk about the most simple and “losing” information. If you are interested in how to combine text from two cells into one in Excel, leaving only one value, then you will need the "merge" function in the "Cell format" section.

Select with the mouse the cells to be merged.

First you need to open a side window with the contents of the clipboard. To do this, in the "Home" tab, click on the button to display this block in the main Excel window.

  1. Select Format Cells.
  2. In the window that appears, go to the "Alignment" section.

Check the box "Combine cells" and click "OK". The selected fields will be combined into one large one while keeping the contents of the first cell.

Now select with the mouse the cells, the contents of which need to be combined into one. Copy them using the toolbar, context menu, or the keyboard shortcut Ctrl + C.

With the function "Hitch-couple"

  1. Earlier versions of Excel used the Concatenate function to combine text in cells; newer versions of Excel have replaced it with the Concatenate function. To use it:
  2. Click on a blank cell in Excel.
  3. Put the "=" sign and in capital letters enter "Link" or "Link".
  4. In brackets after the formula, click on the cell that should be the first in the merged cell, or enter its address manually (for example, A1). Then put a semicolon, then click or write the second cell. Then put a semicolon and mark the third box. After listing the addresses, press "Enter". The combined text will appear in the field where the formula was entered. It will appear with no spaces between text blocks. To avoid this, hit a space in each individual cell after the text, or after the cell address in the formula, add a space manually by writing "" (space in quotes). The space in the formula is also separated from the rest of the items with a semicolon.

How to combine text from two cells into one in Excel using the "Combine" function

= CONCATENATE (X, Y)

How to wrap a line in excel

How to link two tables

How to split a cell

  1. How to combine multiple cells
  2. How to combine text of cells in Excel

How to divide in Excel

How to make table breaks

How to transfer data from one table to another

How to put another table in a table

How to highlight a column

How to replace period with comma in Excel

“. In this case, X and Y are the values ​​of the coordinates of the cells to be merged.

How to make a list in one cell in Excel

How to combine tables

How to split a cell in Excel

How to save tables

How to sum cells in Excel

How to make excel open

When working with tables in Excel, users often need to combine some cells. By itself, this task is not difficult if there is no data in these cells, i.e. they are empty. But what about a situation when the cells contain any information? Will the data be lost after merging? In this article, we will analyze this issue in detail.

How to merge cells The procedure is quite easy and can be used in the following cases: Concatenation of empty cells.

Concatenation of cells, among which only one contains filled data. First of all, you need to select the cells to be merged with the left mouse button. Then we go to the program menu on the "Home" tab and look for the parameter we need there - "Combine and place in the center". With this method, the selected cells will be merged into one single cell, and the content will be centered.

If you want the information to be located not in the center, but taking into account the formatting of the cell, you should click the small down arrow located next to the icon of combining cells and in the menu that opens, select the item "Combine cells".

This method of merging will align the data with the right edge of the merged cell (default).

In our case, it is required to concatenate cells B2 and D2, which means we write the formula “

With this method of merging, the result is somewhat different: the cells are merged into one, but the breakdown by rows is preserved.

Cells can also be merged using the context menu. To perform this task, select the area to be merged with the cursor, right-click, and then select "Format cells" from the list.

And in the window that appears, select the "Alignment" item and put a tick in front of the "Merge cells". In this menu, you can also select other merging options: word wrap, auto-fit, horizontal and vertical orientation, direction, various alignment options, and more. After all the parameters are set, click on "OK".

So, as we wanted, the cells merged into one.

How to merge cells without losing data

But what about a situation where multiple cells contain data? Indeed, with a simple merge, all information, except for the upper left cell, will be deleted.

And this seemingly difficult task has a solution. To do this, you can use the "CONNECT" function. The first step is to do the following. An empty cell must be added between the cells to be merged. To do this, right-click on the column / row number before which we want to add a new column / row and select the "Insert" item from the drop-down menu.

If you combine this with the function of extracting the first letters from the text -

LEFT

, then you can get a surname with initials with one formula:

We have text in several cells and the desire is to combine these cells into one, merging their text there. The problem is one thing - the button

Merge and Center

in Excel, he knows how to combine cells, but with text the complexity - only the text from the upper left cell remains alive.

To combine cells with text concatenation (as in Word tables), you will have to use a macro. To do this, open the Visual Basic Editor on the tab

Developer -

Visual Basic (Developer - Visual Basic)

or by pressing Alt + F11, we will insert a new program module into our book (menu

Insert - Module

) and copy the text of such a simple macro there:

Now, if you select several cells and run this macro using the Alt + F8 keyboard shortcut or the button

on the Developer tab

(Developer - Macros)

Now double click on the cell where the merged text should fit.

The office-menu setting includes the ability to combine cells without losing data with the ability to separate them with special characters.

Having installed the add-on (the link for downloading it is located at the end of the article), a new tab “office-menu” will appear on the ribbon, on which there are lossless merge buttons and 2 fields for specifying separators.

Click on the pasted object from the "Clipboard" window. After that, the text of two cells will appear in one cell.

This method is the easiest to implement, but it lacks additional features. You will not be able to select separators, apply one algorithm to multiple rows or columns. All actions will have to be performed manually.
This method is the easiest to implement, but it lacks additional features. You will not be able to select separators, apply one algorithm to multiple rows or columns. All actions will have to be performed manually.

Combine using a formula Now let's move on to more complex methods. One of them is using a formula. Let's consider the procedure in more detail: , Double-click on the cell where you want to place the merged text.

How to link two tables

Start writing down the formula. To do this, put the = sign and first click on the first cell. Then add the remaining cells through the & sign. Apply the formula with the Enter key.

Now you can see the contents of the cells, but without the separators. To make spaces appear between words or blocks, you need to complicate the formula. It will take the form = D3 & ”“ & E3. You have now merged the text from the two cells. But what if you need to repeat the same procedure for several lines in a row? It's pretty simple. You need to put the mouse cursor in the corner of the new cell and stretch it to the end of the entire table. How to merge text from two and more cells into one in Excel

Combining the text of several cells into one is a basic function when working with tabular data, with its p.
Combining the text of several cells into one is a basic function when working with tabular data, with its p.

Combining the text of several cells into one is a basic function when working with tabular data, with its help, the content of an arbitrary number of fields can be concatenated as needed and placed in free cells. It is used in the preparation of surname lists of employees, reports of various directions and other operations with large amounts of information. There are two ways to merge cells: with data loss and without loss. In the first case, only one value remains in the merged cell, in the second, all the text is saved. The second method involves at least four options for combining: using two functions ("Concatenate" and "Combine"), using the ampersand symbol and using a macro. In this article, we will take a closer look at how to merge the text of several cells into one in Excel.

In brackets after the formula, click on the cell that should be the first in the merged cell, or enter its address manually (for example, A1). Then put a semicolon, then click or write the second cell. Then put a semicolon and mark the third box. After listing the addresses, press "Enter". The combined text will appear in the field where the formula was entered. It will appear with no spaces between text boxes. To avoid this, hit a space in each individual cell after the text, or after the cell address in the formula, add a space manually by writing "" (space in quotes). The space in the formula is also separated from the rest of the items with a semicolon.
In brackets after the formula, click on the cell that should be the first in the merged cell, or enter its address manually (for example, A1). Then put a semicolon, then click or write the second cell. Then put a semicolon and mark the third box. After listing the addresses, press "Enter". The combined text will appear in the field where the formula was entered. It will appear with no spaces between text boxes. To avoid this, hit a space in each individual cell after the text, or after the cell address in the formula, add a space manually by writing "" (space in quotes). The space in the formula is also separated from the rest of the items with a semicolon.

"Combine" is an improved variation of the "Coupling" function. Its difference is that parameters are entered only once at the stage of writing a function. In particular, you will have to specify the separator once for the contents of all cells. In order to use the "Combine" function, you need to perform the following steps:

Combining the text of several cells into one is a basic function when working with tabular data, with its p.
Combining the text of several cells into one is a basic function when working with tabular data, with its p.

In parentheses after the formula, include a separator in quotation marks ("", ",", "and"), then add a semicolon. The second parameter of the formula is to ignore empty fields. If they should not be taken into account in the formula, write "1", in some versions this parameter is denoted by the word "True" or "False". Then list the cells to be merged, separated by commas, and press Enter.

The fourth way to concatenate cells is to use the ampersand ("&"). For this: A macro in Excel is a service for automating repetitive actions, it is a recorded sequence of functions applied and mouse clicks for later reproduction. It is used when you need to regularly perform common tasks in which the data, but not the structure, change. For example, macros are used in monthly payroll reports that only change the numbers, not the calculation method. Then the macro saves the user from the need to regularly manually adjust and use formulas. For example, let's merge the cells so that their values ​​are surrounded by html tags of the table cell ( This article will cover how to combine cells in an Excel table, as well as how to combine text and other types of data in cells without losing data. Yandex.Images :

In Excel, it is possible to combine an area of ​​several cells or columns into one, as well as combine data in cells through formulas and
In Excel, it is possible to combine an area of ​​several cells or columns into one, as well as combine data in cells through formulas and

Instant fill

which you can find out in a separate article.
which you can find out in a separate article.

It is very easy to combine multiple cells (rows / columns) in Excel. The method is used to combine empty cells or cells, among which only one contains data, for example, a table header.

Combining the text of several cells into one is a basic function when working with tabular data, with its p.
Combining the text of several cells into one is a basic function when working with tabular data, with its p.

To merge, select the area of ​​cells to be merged and click on the tab

the main
(Alignment) suitable option:
Merging cells
Combining options:
Cell Merge Options Home (Alignment)
The result of merging the selected cells:
Using cell concatenation in this way is not the best option for further work with data, for example, sorting and filtering, creating pivot tables. Try to use it in exceptional cases, and better never.
A similar effect, but without consequences, gives cell formatting (
Ctrl + 1
),
tab
Alignment ► horizontally ► center of selection
Format cells window
If it is necessary to combine several cells containing data, then it will not work by standard means, which Excel will kindly warn you about:
Combining cells limitation

But you are persistent, and here is the result, we lost some of the data:

There are no unsolvable problems. A macro will come to the rescue, which will help to combine data from different cells (rows / columns) into one common one without losing data:
There are no unsolvable problems. A macro will come to the rescue, which will help to combine data from different cells (rows / columns) into one common one without losing data:

Sub MergeToOneCell () 'Merge text from different cells into one merged

Const sDelim As String = "" 'Separator, replace with the desired character by adding it to "" &Dim rCell As Range

Dim sMergeStr As String
Dim sMergeStr As String

If TypeName (Selection) <> "Range" Then Exit Sub &With Selection For Each rCell In .Cells sMergeStr = sMergeStr & sDelim & rCell.Text Next rCell Application.DisplayAlerts = False

.Merge Across: = False &Application.DisplayAlerts = True .Item (1) .Value = Mid (sMergeStr, 1 + Len (sDelim))

End With

  • End Sub
  • The result of the macro operation, the combined text in one cell:
  • The result of combining using a macro
How to combine cells using formulas
How to combine cells using formulas

1. The easiest way to combine data in cells is using the concatenation sign In the category (ampersand):

Concatenating cells with &

For adding

into the formula press Shift + 7 , on ENG keyboard layout. Cell reference with operator added

, will result in data splicing without a separator. COUPLING You can add a separator using the required separator enclosed in quotes in the formula.

For instance:
For instance:

space "";

comma and space ",";

semicolon ";" etc.

Concatenate cells with & with delimiter

2. The next option to combine strings is a function (CONCATENATE), one of the text functions designed to combine two or more text strings into one. :

Starting with Excel 2016, it is still a working throwback and is used within the framework of version compatibility.
Starting with Excel 2016, it is still a working throwback and is used within the framework of version compatibility.

Function syntax: & CONCATENATE (text1; [text2]; ...) COUPLING where

text1 Nuance: do not forget about spaces between words - they must be written as separate arguments and enclosed in brackets, because the text. , a reference to the first cell with data or a text constant, respectively (CONCAT) [text2]

next cell reference or text constant, etc. You can concatenate a text value, a number (date), or cell references. Formula can contain up to 255 elements and up to 8192 characters.

Concatenate cells using the CONCATENATE function

The function glues the text without delimiters, the problem is eliminated by adding a delimiter in quotes between the arguments. Combining text will not be a problem, but combining numbers or dates may result in different results.

, will result in data splicing without a separator. Formula For example, you want to combine text and a number in a cell with a formula rounded to an integer, or add a date.

The number will be added as it is stored in the program memory, the date is a number, therefore it will show itself without a format.

To set the desired format for a number or date after combining, you need to add the function
To set the desired format for a number or date after combining, you need to add the function

TEXT Formula Concatenate cells with numbers and dates

Lack of data aggregation using an operator and functions , consists in the fact that all parts of the text need to be specified in separate arguments, so joining a large number of cells is a very tedious task.

Concatenate cells using the CONCATENATE function

The solution appears starting with Excel 2016, which includes two new functions. , (CONCAT) and

  • Range2 (TEXTJOIN), which will be discussed below.
  • 3. Function CONCEPT ;
  • , which completely fulfills the functionality of its predecessor, but also works with data ranges. Function syntax:

, will result in data splicing without a separator. CONCEPT (text1; [text2]; ...) where text is the element to be appended: a string or an array of strings, such as a range of cells.

The number will be added as it is stored in the program memory, the date is a number, therefore it will show itself without a format.

can contain up to 253 arguments.
can contain up to 253 arguments.

If the concatenated string contains more than 32767 characters (cell entry limit), the VALUE! Error will be thrown. and functions :

Concatenate a range of cells using the CONCAT function
Concatenate a range of cells using the CONCAT function

Works without problems, but if you want to join a range with a separator,

will not help.

4. The best option for combining is function

Добавить комментарий