Prantick
Merging different rows into one is one of the most common tasks performed in Excel. You can merge different rows into one by using the “Merge and Center” command. But in this method, the merged row will only show the upper left row’s data and you will lose the data of all other rows. In this article, I will discuss several techniques by which you will be able to merge rows in Excel without losing any data.
Look at the following Dataset. Here we have names of different customers in rows: 4 to 10. We want to get the names of all customers into one row. So we need to merge these rows.
Download Excel Workbook
Ways to Merge Rows in Excel without Losing Data
1. Using Clipboard
Merging rows using the Clipboard is the easiest method. First, we have to open the Clipboard from the Home Tab by clicking the downward arrow.
Now we have to select the data we want to merge and then copy the data by pressing CTRL+C. The data will be shown on the Clipboard.
Now we need to double click on an empty cell and click on the Paste All box in the clipboard. It will merge all the data in the cell. Now you can customize your data representation as you want, for example putting commas (,) between different names.
Read more: How to Merge Rows and Columns in ExcelÂ
2. Using The CONCATENATE Function
We can also merge rows into a single row using the CONCATENATE function. Select an empty cell and type,
=CONCATENATE (Select the data cells one by one)
It will put all the data into a single cell.
You can put commas or space between the names by modifying the formula,
=CONCATENATE (A4,", ",A5,", ",A6,", ",A7,", ",A8,", ",A9,", ",A10)
You can also merge multiple columns of different rows into a single row by using the CONCATENATE formula. After typing the formula you need to select the cells of a row then put a comma and select cells from another row and so on.
Read more: How to Merge Rows in Excel Based on Criteria
3. Using Equal Formula
Select an empty cell and press =, Then select the first cell, type &, select the second cell, again type & select the third cell, and so on.
After pressing ENTER, you will get all the data in the selected cell
Similar Readings:
- How to Merge Duplicate Rows in Excel (3 Effective Methods)
- Excel Merge Rows with Same Value (4 Ways)
- How to Combine Multiple Rows into One Cell in Excel
4. Using Notepad
You can also merge multiple rows into a single row by using Notepad. Copy data from all the rows into an empty Notepad.
Now you can customize your data in the notepad easily. For example, you can put all the names in one line or you can insert a comma between two names. After customizing your data, copy it back to an empty row of your Excel file.
5. Using the TEXTJOIN Function
Select an empty cell and type,
=TEXTJOIN (" ", FALSE, select all the cells one by one)Â After selecting the cells for our dataset, the formula will look like,
=TEXTJOIN (" ", FALSE,A4,A5,A6,A7,A8,A9,A10)
After pressing ENTER, you will get all the data in the selected cell
Conclusion
By using any of the methods you can easily merge different rows into one without losing any data in Excel. If you want to merge two cells or merge two columns, you can take a look at the following article. Feel free to leave a comment if you have any confusion or if you know any other method for merging rows that we might have missed.
