Contents
This guide talks about how to concatenate multiple rows in Excel. Learn how you can quickly merge multiple rows in Excel without data loss, without any macros or add-ons. Only with formulas!
Concatenating rows in Excel is one of the most common tasks in Excel that we see everywhere. The trouble is that Microsoft Excel does not provide any suitable tool for this task. For example, if you try to match two or more rows in an Excel worksheet with the command Merge & Center (Merge and Center), which is on the tab Home (Home) in the section Alignment (Alignment), you will get this warning:
The selection contains multiple data values. Merging into one cell will keep the upper-left most data only. (Only the value from the top left cell of the range is kept in the merged cell. The rest of the values will be lost.)
If you press OK, only the value from the top left cell will remain in the merged cell, all other data will be lost. Therefore, it is obvious that we need to use a different solution. Later in this article, you will find ways to merge multiple rows in Excel without losing data.
How to concatenate rows in Excel without losing data
Problem: There is a database of customer information in which each row contains certain details such as product name, product code, customer name, and so on. We want to concatenate all rows related to a particular order to get this result:
When you need to merge rows in Excel, you can achieve the desired result in this way:
How to concatenate multiple rows in Excel using formulas
Microsoft Excel provides several formulas to help you combine data from different rows. The easiest way to remember a formula with a function CONCATENATE (CLUTCH). Here are some examples of how you can concatenate several lines into one:
- Concatenate strings and separate values with a comma:
=CONCATENATE(A1,", ",A2,", ",A3)=СЦЕПИТЬ(A1;", ";A2;", ";A3) - Concatenate strings leaving spaces between values:
=CONCATENATE(A1," ",A2," ",A3)=СЦЕПИТЬ(A1;" ";A2;" ";A3) - Concatenate strings without spaces between values:
=CONCATENATE(A1,A2,A3)=СЦЕПИТЬ(A1;A2;A3)
I am sure that you have already understood the main rule for constructing such a formula – you need to write down all the cells that need to be combined separated by commas (or semicolons if you have a version of Excel), and then enter the necessary separator between them in quotation marks; for example, “,” is a comma with a space; “” – it’s just a gap.
So let’s see how the function CONCATENATE (CONCATENATE) will work with real data.
- Select an empty cell on the worksheet and enter the formula in it. We have 9 rows of data, so the formula will be quite large:
=CONCATENATE(A1,", ",A2,", ",A3,", ",A4,", ",A5,", ",A6,", ",A7,", ",A8)=СЦЕПИТЬ(A1;", ";A2;", ";A3;", ";A4;", ";A5;", ";A6;", ";A7;", ";A8) - Copy this formula to all the cells in the row, you should end up with something like this:
- Now all the data is merged into one line. Actually, concatenated strings are formulas, but you can always convert them to values. For more information about this, read the article How to replace formulas with values in Excel.
