Many users have reported that while trying to merge and combine columns in Microsoft Excel, they lose data except for that in the left-most column. To counter this problem, you should use some formula. We will show you the way in this post.
Combine columns without losing data in Excel
You can Merge and combine columns without losing data in Excel using the following two methods:
- Using an Operator
- Using the CONCATENATE formula.
Let us see both these methods in detail.
1] Using Operator
You can merge and combine columns without losing data in Excel using an operator. The syntax for the same would be:
=<First cell with text in first row>&<First cell with text in second row>&<First cell with text in third row>&...<First cell with text in last row>
While using this formula, please make sure that the first cells with the text for each column are in the same row. Also, in case any cell is blank in any of the cells in the columns, it would be skipped while merging.
Eg. Assume that you need to merge 3 columns of text. The columns are column A, B, and C. In all the three columns, the first row with the text is row 3 (it is important for the first texts in each column to be in the same row). Also, let us assume the texts in the columns are till row 7.
Then the formula for merging will be:
=A3&B3&C3
Enter this formula in row 3 of the column in which you need the merged text. Eg, if you need the merged text in column E, place the formula in cell E3. Hit Enter to get the merged text of cells A3, B3, and C3 in cell E3.
Click anywhere outside cell E3 and then back on cell E3 to highlight the Fill option. Then drag the formula across the column to cell E7. This will help in displaying the merged text of all the columns in column E.
However, the issue is that the merged text would have no spaces between the initial texts. If you wish to add spaces, modify the formula, and make it as follows:
=A3&" "&B3&" "&C3
Enter this formula in cell E3 and use the Fill option to extend it across the column.
2] Using the CONCATENATE formula
For using the CONCATENATE formula for this issue, the syntax would be:
=CONCATENATE(<first cell of the first column>:<first cell of the first column>)
Eg. In the above-mentioned example, the formula would become =CONCATENATE(A3:C3)
In this case, A3:C3 would represent the range of the columns. The CONCATENATE formula is especially helpful when you need to would with a large number of columns, and mentioning them individually is not possible.
Hope this helped.
